How to restore archive logs from rman backup?

If the file paths are the same on the production and the standby, then make sure the rman configuration settings are the same. Make sure the init.ora or spfile are identical for the archivelog locations.

I think you also need to crosscheck archivelogs so that the catalog can discover the existing archivelog(s).

Normally when you issue recover database with RMAN, it automatically looks for the required archivelogs inthe location specified.

R.

Yes, the directory structures are both servers are identical.  Also, the spfile does not seem to show any RMAN configuration settings.  When I do a "show all" from RMAN prompt, both servers configurations are also identical except for one setting:  "configure snapshot controlfile name" points to a different folder on the prod server than it does on the standby server... but I think that is irrelevant for this discussion, right?

When I issued crosscheck archivelog all from th standby server it crosschecked 13 objects, but again it still didn't use the .bkp file which I copied to the flash recovery area on the G: drive.  Instead it found archive logs with .arc extension in two locations.

The first:  G:\oracle\product\10.2.0\flash_recovery_area\PTMN\ARCHIVELOG\2009_08_05
The second: L:\archive_logs\ptmn\ptmn\autobackup\2009_08_05

Am I unable to restore my archive logs from a .bkp file? I'm sure there's a way since rman does the backup in that format to begin with, but I'm struggling with the syntax still....  I thought it was like this:  recover archivelog from backup 'insert path here' but obviously I am wrong.

Any more help would be much appreciated.

Actually, no I am wrong.  I have a .bkp file in:  L:\archive_logs\ptmn\ptmn\autobackup\2009_08_05 which I copied as part of the restore database operation that I did yesterday, but during the crosscheck RMAN says it found files here:  L:\archive_logs\ptmn\ptmn\archivelog\2009_08_05, but that directory does not exist!  So... where is it pulling that information from (the inventory of the prod database since that's the DB instance that was recently restored???), and how do I clear that and make it look in the right locations?

I can not understand your plan.

1. the primary db sends archived logs to the standby
2. the standby received the logs and applied them
3. the DMLs on the standby produce own archive logs (if the DB is in archivelog mode).

What is your intention?

I do not believe that RMAN will see archived logs from other DB.
Also this what you want the register already should exist on the standby due the functionality of the standby.

again - what is your intention?

1- Yes, this is a manual process at the moment just for testing purposes until we prove it works.  I copy the archive logs in the form of .bkp files from prod server to standby server.  Directories are identical.

2- It received them because I copied them, but no, it did not apply them.  That's my goal.  I have my database as of 6:30am yesterday.  I have two .bkp files (1 contains spfile and controlfile, the other contains just archive logs), and I want to "roll forward" the archive logs.

3- What is a DML?

Rigth now if I do list backup or crosscheck archivelog I get absolutely nothing.  It appears my catalog is not seeing anything, even though I restored the controlfile from the latest autobackup.  

Can you give me step by step instructions on how to restore archive logs?  I hope that's a little clearer.

Thanks for your help so far.

What are these .bkp files? Is that the format you've specified for naming backup in prod? If so, you must configure the standby to use the same format.

DMLs are INSERT, UPDATE, DELETE...

If the format is unknown to the standby, it would not find the archivelogs with these extensions.

R.

Compare the log_archive_format paramter in your prod and standby instances. Are they the same?

What about log_archive_dest_1 parameters? Are they the same?

R.

use command

RMAN>catalog backuppiece < path/PTMN_ARCH_K1KLU4EK_1_1.BKP>;

this way your controlfile will known about this new piece.

After attempting to catalog the backuppiece, I get:  RMAN-20001: target database not found in recovery catalog.

try to do this using the controlfile not connected to the catalog then sync;let me know

Ok, it looks like the manual catalog operation worked after disconnecting from the catalog, but then when I re-connect and do "resync catalog" I still get RMAN-20001: target database not found in recovery catalog.

would you please also provide
select dbid from v$database from both prod and standby?

thnaks

Clarification of it-rex's last 2 posts:

Run RMAN as:

rman target /

So it connects to the controlfile repository, not the catalog. Sounds like either you are using multiple catalogs, or that instance is not catalogued yet. So just use the controlfile, it is the ultimate source of the RMAN data.

Then use the catalog backuppiece command it-rex recommended (I've made a minor syntax change for clarity)

RMAN> catalog backuppiece /backups/foo.bkp;

I strongly agree with schwertner:  http:#25033898

What you are doing is unusual. Once the standby is created, just turn on managed recovery and the logs will start shipping (if they are configured as a primary + standby pair properly).


Another option, if you are dealing with archivelog backups, you can use RMAN backup 'as copy' for the archivelogs, so they are not in a backupset, then they could simply be ftped / scped directly into the archive dest on the DB you want to recover, with no tricks.

Prod query:
SQL> select dbid from v$database;

      DBID
----------
3844150329

SQL>


Standby query:
SQL> select dbid from v$database;

      DBID
----------
3844150329

SQL>

I assume that's good since it knows which DB ID it's attempting to become.  

@mrjoltcola:  I already did the catalog operation you and it-rex mentioned while disconnected from the catalog, thus, using the control file.  However, you need to re-connect to the catalog in order to do the resync operation -- otherwise I get an error saying operation not permitted while not connected to a catalog.

make sure you are connecting to the right caralog and using the right target database and then issue register database;
from rman

The target and catalog databases are definitely correct -- the standby server has no link back to the target server... the tnsnames has no entry to the SID on that server.

When I tried register db I get:

RMAN> register database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 08/06/2009 13:47:25
RMAN-08040: full resync skipped, control file is not current or backup

RMAN>

OOh this is a stand by DB..
you are not supposed to register stand bys to the catalog as the control file is flagged as differnet which confuses the catalog;

let me do some more research;and I will get back to you;
but I would say that  99% you will not be able to use a catalog with a standby controlfile.

got it
you will need to switch over to the standby database, configure the recovery catalog, and then switch back.

it is areported bug

1) Standby databases WILL have the same DBID

2) RMAN is standby aware, if you use it correctly

3) You should be using a recovery catalog doing the type of work you are doing. The catalog needs to be accessible from both primary and standby. When you take backups on the primary or standby, the backups will automatically be recorded in the central catalog. This is the only way to do this in a Dataguard cluster.


You are jumping through hoops when you don't need to, and I think the root problem goes back to the fact that may have taken the initial backups of the archivelogs without connecting to a catalog. If you always connect to the catalog, then you can take/restore backups from either primary or standby node, because it is all the same DBID.

I'm not sure I understand.  The assumption and purpose of this restore process is to emulate a disaster scenario where the prod server and db are DOWN and completley unavailable.  Assuming we have the FTP/transfer process complete and all necessary backup files exist on the standby server, what are the steps we need to take to use a backuppiece to roll forward the standby database using the archive logs?

@mrjoltcola
2) RMAN is standby aware, if you use it correctly

3) You should be using a recovery catalog doing the type of work you are doing. The catalog needs to be accessible from both primary and standby. When you take backups on the primary or standby, the backups will automatically be recorded in the central catalog. This is the only way to do this in a Dataguard cluster.

this is a Bug 3298797 ;I am not making this up.

@it-rex: I am looking now. Give me a sec.

@NJDfan1711: Can you clarify: Are you using an RMAN catalog when taking all backups, and even when duplicating the database for standby, or did you at some point do the backups only from the controlfile?

do that

switch over to the standby database, configure the recovery catalog, and then switch back.

No no no...guys, assume the prod server/db is unavailable.  Disaster has struck, and I'm left with the backup files on the standby server/db -- it has its own catalog!  The two are not interconnected in any way.  

Here's what I am doing at this very moment:

- Restoring the standby database as of 6::30am yesterday using the backupsets created from the target db that I manually copied to the standby server.

-Then, the goal is to use the backuppiece created from the target server yesterday at 12:30pm to restore te archive logs to that point in time.  They have been manually copied to the standby server in the flash recovery area.  What is the command syntax or how do I do this?

@it-rex:

1) It is not a public bug, and is specified for 10.1 so be careful about giving support here based on the Metalink info.
2) He is running 10.2
3) He is not using Grid Control to do this that I am aware of, and that is what the bugs refer to.
4) He is not trying to take a backup of his standby, he is trying to restore the standby which should already be properly registered in the catalog.

BUT he says he is not using a catalog, so we are mainly in a backup and recovery scenario. So lets regroup and understand what he really wants to do.

He cloned the primary, but I do not see that he ever activated it in the ROLE of standby, I do not see that he ever started managed recovery, etc. So is the database open? If so, then retrace all the way back to where you tried to catalog the backup piece while connected to some unknown RMAN catalog:

http:#25035741

When you performed that step, were you connected to a catalog or not?

Then here you tried to register the standby database:

http:#25036109

But you should not do that, the database should already be registered.

So lets get one thing clear before proceeding:


Do you want to do this with an RMAN catalog or not? If so, please give the history of the catalog.

rman target / catalog=rman/pass@CAT
RMAN> list incarnation;

I'm not using dataguard, but in the literal sense, yes, I mean standby as a different host server with a different database+catalog, simply sitting idle waiting to be used if disaster were to strike and a problem occurred with the main server/db.

The select * query is extremely long and I cannot capture it all in my command prompt.  

init.ora from "standby" server:
 
ptmn.__db_cache_size=436207616
ptmn.__java_pool_size=4194304
ptmn.__large_pool_size=4194304
ptmn.__shared_pool_size=159383552
ptmn.__streams_pool_size=0
*.audit_file_dest='G:\oracle\product\10.2.0\admin\PTMN\adump'
*.background_dump_dest='G:\oracle\product\10.2.0\admin\PTMN\bdump'
*.compatible='10.2.0.3.0'
*.control_files='G:\ORACLE\PRODUCT\10.2.0\ORADATA\PTMN\CONTROL01.CTL','G:\ORACLE\PRODUCT\10.2.0\ORADATA\PTMN\CONTROL02.CTL','G:\ORACLE\PRODUCT\10.2.0\ORADATA\PTMN\CONTROL03.CTL'#Restore Controlfile
*.core_dump_dest='G:\oracle\product\10.2.0\admin\PTMN\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='PTMN'
*.db_recovery_file_dest='G:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PTMNXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='G:\oracle\product\10.2.0\admin\PTMN\udump'
 
 
init.ora from "target" server:
 
ptmn.__db_cache_size=754974720
ptmn.__java_pool_size=8388608
ptmn.__large_pool_size=8388608
ptmn.__shared_pool_size=645922816
ptmn.__streams_pool_size=41943040
*.aq_tm_processes=0
*.audit_file_dest='G:\ORACLE\PRODUCT\10.2.0\ADMIN\PTMN\ADUMP'
*.background_dump_dest='G:\ORACLE\PRODUCT\10.2.0\ADMIN\PTMN\BDUMP'
*.compatible='10.2.0.1.0'
*.control_files='G:\ORACLE\PRODUCT\10.2.0\ORADATA\PTMN\CONTROL01.CTL','G:\ORACLE\PRODUCT\10.2.0\ORADATA\PTMN\CONTROL02.CTL','G:\ORACLE\PRODUCT\10.2.0\ORADATA\PTMN\CONTROL03.CTL'
*.core_dump_dest='G:\ORACLE\PRODUCT\10.2.0\ADMIN\PTMN\CDUMP'
*.db_block_size=8192
*.db_cache_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='PTMN'
*.db_recovery_file_dest='L:\Archive_Logs\PTMN'
*.db_recovery_file_dest_size=32212254720
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PTMNXDB)'
*.fast_start_parallel_rollback='FALSE'
*.filesystemio_options='SETALL'
*.java_pool_size=0
*.job_queue_processes=10
*.large_pool_size=0
*.log_archive_dest=''
*.log_archive_start=FALSE
*.nls_length_semantics='BYTE'
*.open_cursors=3000
*.optimizer_index_cost_adj=40
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=681574400
*.processes=700
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.sga_max_size=1468006400
*.sga_target=1468006400
*.shared_pool_size=0
*.streams_pool_size=41943040
*.undo_management='AUTO'
*.undo_retention=900
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='G:\ORACLE\PRODUCT\10.2.0\ADMIN\PTMN\UDUMP'
*.utl_file_dir='d:\mapics\9_0\Server\PTMNLOGS'
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:

Please answer my questions from my last two messages that keep getting ignored. Before I will give any more advice I will wait for those answers. Thanks.

@mrjoltcola:  I think you're starting to understand.  Most of what you said to it-rex is correct.  No dataguard, no grid control, no "standby role".  Just a backup server with a database and catalog, similar if not identical to the way the target server is setup.

I take a full RMAN backup with control file, spfile, archive logs, and datafiles.  I restore, recover, open resetlogs, and all is good.  Time goes by, data is changed, and archive logs are created from prod server.  I copy them to the backup or "standby" server.  My prod server crashes about an hour later.  I want to restore my archive logs that were just taken recently, to bring my backup/standby server to a current state, more current than the complete rman restore that was done several hours ago earlier in the day.

When I tried to catalog the backup piece WHILE connected to the catalog on the backup server, I got an error saying target database not in recovery catalog.  That's when, with the help of this topic, you guys told me to disconnect from the catalog and use just the control file.  I don't know if this is advised, but I am just following your guidance.  With that, it worked and was properly catalog in the CONTROL FILE.  Then, you guys suggested to do a resync.  I re-connected to the catalog since you have to he connected to do a sync, and when I issued the command, I got the same error. That's where I'm stuck, and I am still unsure of the proper syntax to say "restore archive log from backup piece..."

Ok, we are getting somewhere.

1) The RMAN catalog, is it the same catalog that you were using for the backups of the primary?

2) Since you have opened with resetlogs, the bad news, you cannot recover with those archivelogs unless you use flashback or restore from the original backup image prior to the open resetlogs. You've created a new incarnation and a new log sequence. With 10g and 11g it is possible to recover and roll forward by applying logs ACROSS resetlogs boundaries, but it requires you flashback / restore to a point prior to the last SCN before the recover step. Once you open resetlogs on the copy, it is diverged from the primary (primary goes down Main Street, clone goes down First Street). So you have to head back to First and Main intersection, and recover.


NOTE: I have not suggested you resync or register anything. But the confusion starts when discussing standby, because we immediately think "Data Guard" cluster.

Yes, sorry for the confusion.  I just saw that "standby database" is actually an industry term which seems to describe an online replication of some sort.  I am not doing something that advanced or complex (I don't think).

To answer #1, no, I do not believe it is the same catalog -- I've said now a few times, each server has its own catalog.  Target server = primary db + catalog.  Backup server = backup db + catalog.

For #2, I think I understand what you're saying.  Where can I go from here?  I'm trying to get back to a point where I am ready to attempt restoring archive logs again, but after just finishing the restore+recovery, I get an error saying data file 5 needs media recovery after doing open resetlogs.

these are 2 typical database using the same name and dbid;therfore they can not use the same catalog;
mrjoltcola;please advise.

is the physical structure of thses 2 servers exactly the same?
disk wise?
also please show  both

select * from v$instance
select * from v$database

 for both servers
@mrjoltcola understood and will do;do not be mad at your student;)

These are different options below to accomplish your standby solution. This is a very complex question and is honestly best done by voice, because the nature of the options differ and need to be explained. I'll do my best.

In no particular order of value or complexity, some options:


1) Keep a non-dataguard "standby" ready for recovery, but simply don't open it. Just keep it in recovery mode and apply logs as you need to. That way you won't create a new incarnation. It is the act of "open resetlogs" that you need to delay until needed. Keep in mind, you need to be constantly shipping logs or backupsets over to the standby for availability, or must have to delay the application of the logs until the failure, and for that reason, I really don't see any advantage to this step, but we used to do this prior to data guard with manual scripts.

2) If you want to open the "standby", for testing, or whatever, then configure Oracle flashback database. It will allow you to flashback and recover again and again. The flashback will send you back in time, then you can recover again.

OR

3) Simply restore a full backup and recover as far as you can, using the backupsets / archivelogs. This option is a delayed response option, in that you have to wait until the disaster to start all of the restore / recovery work. If going this route, at least periodically test the backups by doing this restore.


What is to be gained from using separate catalogs? I do not advise that. It would solve your problem of having the correct history available to RMAN without any "catalog operations", this is the whole point of the centralized catalog.

Yes, the directory structures are the same.  Drive letters are the same.  Except target server is on a SAN with RAID 5, and backup server has local RAID 1.

Query from backup server:
 
SQL> select * from v$instance;
 
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 ptmn
TXDR
10.2.0.4.0        06-AUG-09 MOUNTED      NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO
 
 
SQL>
 
 
Query from target server:
 
SQL> select * from v$instance;
 
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 ptmn
DATA1
10.2.0.4.0        06-AUG-09 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO
 
 
SQL>

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:

@it-rex: Noone is mad :), there was some miscommunication in this thread. I think we are closing on a solution.


@NJDfan: At this point, if you've opened previously with resetlogs, your only option to get back to where you can restore archive logs from the primary is to start over and restore the database again. Though I am unclear how if you had previously opened with resetlogs, how you are in a state of requiring media recovery.

This is a very complex topic to deal with over this forum. :(

Since I'm running out of 1-2-3s and a-b-cs I'm switching to Roman Numerals. :)

I. There is the solution to rescue you from a minor mess that is based on a simple mistake.

If you cannot open without further recovery (file 5 needs media recovery) then RMAN is looking for the next archive log. Perhaps it was not properly backed up, or rotated out in the first place, or perhaps the catalog command never worked. You can continue this route and probably learn some valuable lessons...


II. There is a long-term, proper solution to accomplish what you originally set out to do.

Start over and do it right.

I don't know why I am in that state either.  I did a restore database and everything seemed fine.  Then when I issued "recover database" it appeared to complete, but told me openresetlogs would result in that error.  So I did it anyway, and of course, got the error.

Now, if I try to issue restore database again, it tells me all datafiles already restored.  If I issue restore datafile 5, it also says it's been restored.  

select resetlogs_time from v$database from the backup one;

Do not mix the terms "target "with "production/Original" DB
as target means the db that rman will serve using eithet a catalog or controlfile;
so any DB could be a target DB.

I also I gree with mrjoltcola these are your current option.

If the restores are not executing again, you may try restore with the 'force' option.

I've needed it at one point in the past when RMAN was trying to be too smart and thought the file did not need restoration.

The situation you are in is simply lack of enough archive logs to finish the recover. You can continue down the path and restore the archive logs with your original scenario, but I'm dubious, because if you ever opened your database, then it should open now, unless you had repeated a restore.

Hi guys.  I am just about to close this topic since I have made some progress now, but I had just one last question.  Here's what I did:

Deleted all files and references to any backups and logs other than 8/5/09 which is the date I want to roll forward to.  Restored the controlfile from autobackup from that date.  Restored the datafiles again, then did a recover and open with resetlogs.  It succeeded.  Then, when connected to my catalog on the backup server, I did a 'catalog backuppiece' using the backup from 12:30 on 8/5/09.  It worked.  Then I had to register my database with the catalog.  It worked, and a resync also succeeded after  that.  Finally I ran 'restore archivelog all' which seemed to complete successfully as well.  I can see the new .arc files in the directory I expect...however... I thought this step was the part where the database gets "rolled forward".  But, when I check my ERP program for the data, I did not see anything new after the restore I did from 6:30am.  So here's my last question:

Does "restore archivelog all" actually import the archive logs, or does it just extract them from my .bkp file and put them on the hard-drive?  If the latter, then what is the command to actually make the database import those .arc files?  Is it a recover database?

If it's ok with you guys, I'd like to split the points, 250 a piece, between mrjoltcola and it-rex, since you both offered helpful advice throughout the troubleshooting process.

Thanks again.

to be honest I think you need to split these points between schwertner;mrjoltcola as she is the first one pointed us that there is some miscommunication.
see ID: 25033898

I do agree the comment was somewhat helpful to determine what I was after, but that was the only time we heard from that person and it didn't resolve what my actual goal was.  You and mrjoltcola stuck with the issue throughtout many posts in order to get to the proper resolution.

With that said, any ideas as to my last question?

Also please make sure to take my recommendations in consideration, such as using a centralized RMAN server. That way there is no registration, etc. needed. You simply run RMAN from the replica copy, connecting as the original database (since the DBID matches) and restore. As far as the catalog is concerned, it is the same database. Restores are easy, but just be careful about performing _backups_ from different copies of the same database using a central CAT, that will cause issues if not done right.

Make sure to thoroughly test your DR scenario once or twice, and document it. Trust me, if/when you actually need it, you may have forgotten our conversation and some of the steps, and that document will be invaluable.

I definitely consider all recommendations and advice given to me, that's why I'm here :)

I am also documenting as much as I possibly can so that I remember what I did to get it to work.

Where I am now:  I did a restore archivelog all which took two different backupsets from 08/0509 and 08/06/09 and restored the logs into a folder based on today's date, 08/07/09.  I did a crosscheck and a list backup and the catalog apperas to be aware of the files. I can see them on the disk and they appear the right size.  They start with sequence # 4299 and go through 4324.  

If I issue recover database it appears to work, but finishes in 0 seconds and does not error out.  That's strange. The same happens if I do recover database until sequence = 4324

Then if I do recover database until time 07-AUG-09, it starts, but errors out with "datafile 1 must be restored from backup created before 07-AUG-09".

And finally, if I do recover database until time 06-AUG-09 it RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIMEor RECOVERY WINDOW is before RESETLOGS time.


Your thoughts?  I feel like I am inches away from success... =/