RMAN restore - log_file_name_convert

[Roshan]

Hello,

I am performing an RMAN restore. Below is my script. The issue is the redo log restore. The redo logs are found on multiple locations on production. How do I do log_file_name_convert?

rman target /  
    startup nomount  
     
    run {  
    allocate channel c1 device type disk;  
    allocate channel c2 device type disk;  
    allocate channel c3 device type disk;  
    allocate channel c4 device type disk;  
    set newname for database to '/data/WINSDB';
    SET ARCHIVELOG DESTINATION TO '/archive/WINSDB/'
    set newname for tempfile 1 to '/data/WINSDB';
    duplicate database to 'WINSDB' backup location '/archive/winsbackup'
   
    recover database;  
    alter database open resetlogs;  
    release channel c1;  
    release channel c2;  
    release channel c3;  
    release channel c4;  
    }  

Thanks and Regards,
Roshan

[ErmanArslansOracleBlog]

Hi,

Did you check the definition of log_file_name_convert?

It says:

LOG_FILE_NAME_CONVERT = 'string1' , 'string2' , 'string3' , 'string4' , ...
Where:

string1 is the pattern of the primary database filename
string2 is the pattern of the standby database filename
string3 is the pattern of the primary database filename
string4 is the pattern of the standby database filename

You can use as many pairs of primary and standby replacement strings as required. You can use single or double quotation marks.

The following are example settings that are acceptable:

LOG_FILE_NAME_CONVERT = '/dbs/t1/','/dbs/t1/s_','dbs/t2/ ','dbs/t2/s_'

Consider the standby database as your duplicate/clone database.

[Roshan]

Thanks Erman :)
I have modified the script and pfile. Now when I have launched the restore it says



RMAN> @RW.sql

RMAN>   restore controlfile from '/archive/winsbackup/curr.ctl';
Starting restore at 21-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1422 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/21/2016 19:30:33
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN>    alter database mount;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/21/2016 19:30:33
ORA-00205: error in identifying control file, check alert log for more info

RMAN>
RMAN>     run {
2>     allocate channel c1 device type disk;
3>     allocate channel c2 device type disk;
4>     allocate channel c3 device type disk;
5>     allocate channel c4 device type disk;
6>     set newname for database to '/data/WINSDB/db_%f'; # make sure, you set the parameter db_create_file_dest in your init file
7>     set newname for tempfile 1 to '/data/WINSDB/temp_%f';
8>     duplicate database to 'WINSDB' backup location '/archive/winsbackup';
9>
10>     release channel c1;
11>     release channel c2;
12>     release channel c3;
13>     release channel c4;
14>     }
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=1422 device type=DISK

allocated channel: c2
channel c2: SID=1706 device type=DISK

allocated channel: c3
channel c3: SID=1848 device type=DISK

allocated channel: c4
channel c4: SID=1990 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 21-NOV-16
released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/21/2016 19:30:34
RMAN-05501: aborting duplication of target database
RMAN-05502: the target database must be mounted when issuing a DUPLICATE command

RMAN> **end-of-file**


----- The autobackup is in $ORACLE_HOME/dbs

All my backup files(including controlfile) are in /archive/winsbackup folder. Why does it search for autobackup of ctrl file?

Thanks,
Roshan

[Roshan]

I managed to restore the controlfile using

RMAN> run {
set controlfile autobackup format for device type disk to '/opt/app/oracle/product/11.2.0.4/dbs/%F';
restore controlfile from autobackup;
}2> 3> 4>


Now I am getting



allocated channel: c4
channel c4: SID=1563 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 21-NOV-16

released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/21/2016 19:59:54
RMAN-06509: only SPFILE or control file can be restored from AUTOBACKUP

[ErmanArslansOracleBlog]

Where is your restore command ( I mean the restore command for datafile/database)

You cant use the same syntax for restore of datafiles.

For example, following command will result with "RMAN-06509: only SPFILE or control file can be restored from AUTOBACKUP "

restore datafile 4 from 'C:\ORACLE\PRODUCT\9.2\DATABASE\0TI6L6AD_1_1';

2016-11-21 18:49 GMT+03:00 Roshan [via Erman Arslan's Oracle Forum] <[hidden email]>:

I managed to restore the controlfile using

RMAN> run {
set controlfile autobackup format for device type disk to '/opt/app/oracle/product/<a href="http://11.2.0.4/dbs/%F" target="_blank">11.2.0.4/dbs/%F';
restore controlfile from autobackup;
}2> 3> 4>


Now I am getting



allocated channel: c4
channel c4: SID=1563 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 21-NOV-16

released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/21/2016 19:59:54
RMAN-06509: only SPFILE or control file can be restored from AUTOBACKUP

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/RMAN-restore-log-file-name-convert-tp1576p1579.html

To start a new topic under Database, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

--

[Roshan]

  run {  
    allocate channel c1 device type disk;  
    allocate channel c2 device type disk;  
    allocate channel c3 device type disk;  
    allocate channel c4 device type disk;  
    set newname for database to '/data/WINSDB/%f'; # make sure, you set the parameter db_create_file_dest in your init file
     
    set newname for tempfile 1 to '/data/WINSDB/%f';
       
    restore database from '/archive/winsbackup';  
    switch datafile all;  #use when using the set command
    switch tempfile all;  #use when using set command
    recover database;  
    alter database open resetlogs;  
    release channel c1;  
    release channel c2;  
    release channel c3;  
    release channel c4;  
    }  

[ErmanArslansOracleBlog]

restore database from? what is this syntax?..
anyways;

catalog your backups if they are not already. (Read "catalog start with")
After then do your restore using "restore database".

[ErmanArslansOracleBlog]

Example:

restore controlfile from 'blabla'
alter database mount;
catalog start with 'your backup location/*';
restore database;
recover database;

[Roshan]

Thanks :)
Now it is ok.. I am able to proceed with the restore after doing the catalog.
Thanks for your your support and guidance

[Roshan]

Hi,

the restore completed with some errors. The database is currently mounted. I do not see the redo logs. How can I recover from the archive backup pieces?

alert_WINSDB2.log

Rgds,
ROshan

[Roshan]

I did a recover database

RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27426 and starting SCN of 135096450843
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27425 and starting SCN of 135096396263
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27424 and starting SCN of 135096333862
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27423 and starting SCN of 135096284511
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27422 and starting SCN of 135096077402
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27421 and starting SCN of 135071103030
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27420 and starting SCN of 135070425788
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27419 and starting SCN of 135070298993
RMAN-00567: Recovery Manager could not print some error messages

[ErmanArslansOracleBlog]

You have lots of ORA-19625: error identifying file , are you aware of that?

[Roshan]

Hi,

yes.. but when I do

RMAN> RUN
{
allocate channel c1 device type disk;
    allocate channel c2 device type disk;
    allocate channel c3 device type disk;
    allocate channel c4 device type disk;
  SET ARCHIVELOG DESTINATION TO '/winsdb/archive';
  RESTORE ARCHIVELOG ALL;
  # restore and recover datafiles as needed

}2> 3> 4> 5> 6> 7> 8> 9> 10> 11>

I am getting

RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27424 and starting SCN of 135096333862
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27423 and starting SCN of 135096284511
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27422 and starting SCN of 135096077402
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27421 and starting SCN of 135071103030
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27420 and starting SCN of 135070425788
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27419 and starting SCN of 135070298993
RMAN-06102: no channel to restore a backup or copy of archived log for thread 2 with sequence 27418 and starting SCN of 135069945583

I have the backups of archivelog from /archive/winsdb and i have already catalogued them

Thanks,
ROshan

[Roshan]

RMAN> list backuppiece '/archive/winsbackup/WINS_al_WINSDB_20161120_948237_1'
2> ;

using target database control file instead of recovery catalog

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
942702  942599  1   2   AVAILABLE   DISK        /archive/winsbackup/WINS_al_WINSDB_20161120_948237_1

[ErmanArslansOracleBlog]

catalog (catalog start with) the archive directory, as you did for database backup and retry.

[ErmanArslansOracleBlog]

Now I see you already used "catalog" command for these backups.

Well, check this note then;

RMAN-06100 or RMAN-06102 during restore/duplicate (Doc ID 1615996.1)

[ErmanArslansOracleBlog]

Bytheway, why do you use restore archivelog all command?
After restore, just recover the database and open it.
You need to restore only the archivelogs which are needed for that recovery.

So if your db restore is completed, use recover database command when you re in mount state, use the required archivelogs (only required) to make the database consistent and open it (resetlogs or noresetlogs according to your case)

[ErmanArslansOracleBlog]

Also,

you can check the archive backups are there for the sequences:
Example:

list backup of archivelog from logseq 27418 until logseq 27424  thread 1


But again, it is not required to restore all the archivelogs(all backed up) for being able to restore a datbaase.

[Roshan]

There were some errors during recovery
WINSDB2_pr00_4701.trc

How can I open it?

[Roshan]

SQL> alter  database open resetlogs
  2  ;
alter  database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/WINSDB/1'