Home » RDBMS Server » Backup & Recovery » Getting error ORA-19870: error reading backup piece during database cloning (Oracle 10g R2 on RHEL)
Getting error ORA-19870: error reading backup piece during database cloning [message #499630] Tue, 15 March 2011 09:53 Go to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello

I am trying to clone a database on another server with different direcory structure. So that path on the source db server are /u04 whereas on on target db server it would be /u03

Since this I am testing it on small database initially I have kept all datafiles, backup and archivelogs at /u04 and /u03 on the source and target db servers respectively

Now I have copied the backups from source db server to target and since path is changed, I cataloged it

However during restore I am getting

Quote:

ORA-19870: error reading backup


Here are the session details

RMAN> catalog start with '/u03/oradata/db7fra';

searching for all files that match the pattern /u03/oradata/db7fra

List of Files Unknown to the Database
=====================================
File Name: /u03/oradata/db7fra/DB7/archivelog/2011_03_15/o1_mf_1_16_6qyvpb3w_.arc
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T123018_6qypyv5v_.bkp
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T125006_6qyr3zk4_.bkp
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_ncsnf_TAG20110315T123008_6qypyrz2_.bkp
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T125043_6qyr54to_.bkp

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/oradata/db7fra/DB7/archivelog/2011_03_15/o1_mf_1_16_6qyvpb3w_.arc
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T123018_6qypyv5v_.bkp
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T125006_6qyr3zk4_.bkp
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_ncsnf_TAG20110315T123008_6qypyrz2_.bkp
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T125043_6qyr54to_.bkp

RMAN> run{
2> set newname for datafile 1 to '/u03/oradata/db7/system01.dbf';
3> set newname for datafile 2 to '/u03/oradata/db7/undotbs01.dbf';
4> set newname for datafile 3 to '/u03/oradata/db7/sysaux01.dbf';
5> set newname for datafile 3 to '/u03/oradata/db7/users.dbf';
6> set newname for tempfile 1 to '/u03/oradata/db7/temp.dbf';
7> restore database;
8> switch datafile all;
9> recover database;
10> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 15-MAR-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u03/oradata/db7/system01.dbf
restoring datafile 00002 to /u03/oradata/db7/undotbs01.dbf
restoring datafile 00003 to /u03/oradata/db7/users.dbf
restoring datafile 00004 to /u04/oradata/db7/users.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp
ORA-19870: error reading backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp
ORA-19504: failed to create file "/u04/oradata/db7/users.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
failover to previous backup

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u03/oradata/db7/system01.dbf
restoring datafile 00002 to /u03/oradata/db7/undotbs01.dbf
restoring datafile 00003 to /u03/oradata/db7/users.dbf
restoring datafile 00004 to /u04/oradata/db7/users.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
ORA-19870: error reading backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
ORA-19504: failed to create file "/u04/oradata/db7/users.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
failover to previous backup

creating datafile fno=1 name=/u03/oradata/db7/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/15/2011 14:23:45
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u04/oradata/db7/system01.dbf'

RMAN> list backup of database;


List of Backup Sets
===================

BS Key  Type LV Size
------- ---- -- ----------
2       Full    259.77M
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 180172     15-MAR-11 /u04/oradata/db7/system01.dbf
  2       Full 180172     15-MAR-11 /u04/oradata/db7/undotbs01.dbf
  3       Full 180172     15-MAR-11 /u04/oradata/db7/sysaux01.dbf
  4       Full 180172     15-MAR-11 /u04/oradata/db7/users.dbf

  Backup Set Copy #1 of backup set 2
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:04     15-MAR-11       NO         TAG20110315T123008

    List of Backup Pieces for backup set 2 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    2       1   EXPIRED     /u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp

  Backup Set Copy #2 of backup set 2
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:04     15-MAR-11       NO         TAG20110315T123008

    List of Backup Pieces for backup set 2 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    10      1   AVAILABLE   /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp

BS Key  Type LV Size
------- ---- -- ----------
6       Full    259.88M
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 181124     15-MAR-11 /u04/oradata/db7/system01.dbf
  2       Full 181124     15-MAR-11 /u04/oradata/db7/undotbs01.dbf
  3       Full 181124     15-MAR-11 /u04/oradata/db7/sysaux01.dbf
  4       Full 181124     15-MAR-11 /u04/oradata/db7/users.dbf

  Backup Set Copy #1 of backup set 6
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:32     15-MAR-11       NO         TAG20110315T125008

    List of Backup Pieces for backup set 6 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    6       1   EXPIRED     /u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp

  Backup Set Copy #2 of backup set 6
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:32     15-MAR-11       NO         TAG20110315T125008

    List of Backup Pieces for backup set 6 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    11      1   AVAILABLE   /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp

RMAN>

RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1       1       1   1   EXPIRED     DISK        /u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T123006_6qypyhm8_.bkp
2       2       1   1   EXPIRED     DISK        /u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
3       3       1   1   EXPIRED     DISK        /u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_ncsnf_TAG20110315T123008_6qypyrz2_.bkp
4       4       1   1   EXPIRED     DISK        /u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T123018_6qypyv5v_.bkp
5       5       1   1   EXPIRED     DISK        /u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T125006_6qyr3zk4_.bkp
6       6       1   1   EXPIRED     DISK        /u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp
7       7       1   1   EXPIRED     DISK        /u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T125043_6qyr54to_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T123006_6qypyhm8_.bkp recid=1 stamp=745849807
deleted backup piece
backup piece handle=/u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp recid=2 stamp=745849808
deleted backup piece
backup piece handle=/u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_ncsnf_TAG20110315T123008_6qypyrz2_.bkp recid=3 stamp=745849816
deleted backup piece
backup piece handle=/u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T123018_6qypyv5v_.bkp recid=4 stamp=745849819
deleted backup piece
backup piece handle=/u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T125006_6qyr3zk4_.bkp recid=5 stamp=745851007
deleted backup piece
backup piece handle=/u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp recid=6 stamp=745851008
deleted backup piece
backup piece handle=/u04/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T125043_6qyr54to_.bkp recid=7 stamp=745851044
Deleted 7 EXPIRED objects


RMAN> list backup of database;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    259.77M    DISK        00:00:04     15-MAR-11
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T123008
        Piece Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 180172     15-MAR-11 /u04/oradata/db7/system01.dbf
  2       Full 180172     15-MAR-11 /u04/oradata/db7/undotbs01.dbf
  3       Full 180172     15-MAR-11 /u04/oradata/db7/sysaux01.dbf
  4       Full 180172     15-MAR-11 /u04/oradata/db7/users.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    259.88M    DISK        00:00:32     15-MAR-11
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T125008
        Piece Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 181124     15-MAR-11 /u04/oradata/db7/system01.dbf
  2       Full 181124     15-MAR-11 /u04/oradata/db7/undotbs01.dbf
  3       Full 181124     15-MAR-11 /u04/oradata/db7/sysaux01.dbf
  4       Full 181124     15-MAR-11 /u04/oradata/db7/users.dbf

RMAN>

RMAN> run{
2> set newname for datafile 1 to '/u03/oradata/db7/system01.dbf';
3> set newname for datafile 2 to '/u03/oradata/db7/undotbs01.dbf';
4> set newname for datafile 3 to '/u03/oradata/db7/sysaux01.dbf';
5> set newname for datafile 3 to '/u03/oradata/db7/users.dbf';
6> set newname for tempfile 1 to '/u03/oradata/db7/temp.dbf';
7> restore database;
8> switch datafile all;
9> recover database;
10> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 15-MAR-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u03/oradata/db7/system01.dbf
restoring datafile 00002 to /u03/oradata/db7/undotbs01.dbf
restoring datafile 00003 to /u03/oradata/db7/users.dbf
restoring datafile 00004 to /u04/oradata/db7/users.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp
ORA-19870: error reading backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp
ORA-19504: failed to create file "/u04/oradata/db7/users.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
failover to previous backup

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u03/oradata/db7/system01.dbf
restoring datafile 00002 to /u03/oradata/db7/undotbs01.dbf
restoring datafile 00003 to /u03/oradata/db7/users.dbf
restoring datafile 00004 to /u04/oradata/db7/users.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
ORA-19870: error reading backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
ORA-19504: failed to create file "/u04/oradata/db7/users.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
failover to previous backup

creating datafile fno=1 name=/u03/oradata/db7/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/15/2011 14:36:26
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u04/oradata/db7/system01.dbf'



I have altered permissions on the backup files as well but of no use


oracle@dev-biz:/u03/oradata/db7fra/DB7/backupset/2011_03_15 $ls -ltr
total 545260
-rwxrwxrwx 1 oracle dba  12419072 Mar 15 13:52 o1_mf_ncsnf_TAG20110315T123008_6qypyrz2_.bkp
-rwxrwxrwx 1 oracle dba      3072 Mar 15 13:52 o1_mf_annnn_TAG20110315T125043_6qyr54to_.bkp
-rwxrwxrwx 1 oracle dba    426496 Mar 15 13:52 o1_mf_annnn_TAG20110315T125006_6qyr3zk4_.bkp
-rwxrwxrwx 1 oracle dba     14336 Mar 15 13:52 o1_mf_annnn_TAG20110315T123018_6qypyv5v_.bkp
-rwxrwxrwx 1 oracle dba 272400384 Mar 15 13:52 o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
-rwxrwxrwx 1 oracle dba 272515072 Mar 15 13:52 o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp

RMAN> run{
2> set newname for datafile 1 to '/u03/oradata/db7/system01.dbf';
3> set newname for datafile 2 to '/u03/oradata/db7/undotbs01.dbf';
4> set newname for datafile 3 to '/u03/oradata/db7/sysaux01.dbf';
5> set newname for datafile 3 to '/u03/oradata/db7/users.dbf';
6> set newname for tempfile 1 to '/u03/oradata/db7/temp.dbf';
7> restore database;
8> switch datafile all;
9> recover database;
10> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 15-MAR-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u03/oradata/db7/system01.dbf
restoring datafile 00002 to /u03/oradata/db7/undotbs01.dbf
restoring datafile 00003 to /u03/oradata/db7/users.dbf
restoring datafile 00004 to /u04/oradata/db7/users.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp
ORA-19870: error reading backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp
ORA-19504: failed to create file "/u04/oradata/db7/users.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
failover to previous backup

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u03/oradata/db7/system01.dbf
restoring datafile 00002 to /u03/oradata/db7/undotbs01.dbf
restoring datafile 00003 to /u03/oradata/db7/users.dbf
restoring datafile 00004 to /u04/oradata/db7/users.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
ORA-19870: error reading backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
ORA-19504: failed to create file "/u04/oradata/db7/users.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
failover to previous backup

creating datafile fno=1 name=/u03/oradata/db7/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/15/2011 14:37:50
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u04/oradata/db7/system01.dbf'


Please suggest

Regards,
OraKaran



Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499634 is a reply to message #499630] Tue, 15 March 2011 10:06 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello

BTW I must mentioned that for the changed path of datafiles I intentionally not used db_file_name_convert etc. Just for experiment!

And anyways problem seems related to backups. Right?

Regards,
OraKaran
Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499645 is a reply to message #499634] Tue, 15 March 2011 11:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
How exactly did you restore the control file?
Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499647 is a reply to message #499630] Tue, 15 March 2011 11:15 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello Mahesh

Thanks for the reply

Since I cataloged all the backup and archivelog files with
catalog start with '/u03/oradata/db7fra';


and I copied the controlfile to '/u03/oradata/db7fra/DB7/autobackup/2011_03_15/o1_mf_s_745851045_6qyr55y3_.bkp'

I issued the command
restore controlfile from autobackup;


But now after your reply I fear that it might have restored the wrong one

RMAN> list backup of controlfile;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    11.83M     DISK        00:00:02     15-MAR-11
        BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T123008
        Piece Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_ncsnf_TAG20110315T123008_6qypyrz2_.bkp
  Control File Included: Ckp SCN: 180174       Ckp time: 15-MAR-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    11.83M     DISK        00:00:00     15-MAR-11
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T125045
        Piece Name: /u03/oradata/db7fra/DB7/autobackup/2011_03_15/o1_mf_s_745851045_6qyr55y3_.bkp
  Control File Included: Ckp SCN: 181144       Ckp time: 15-MAR-11

RMAN>



Now 2 things here

1) I don't know what is the controlfile backup at /u03/oradata/db7fra/DB7/backupset/2011_03_15/
it was at similar location on source db server (only /u04 instead of /u03)

2) since i executed the restore directly on console, not via stored script and unable to see it on the session I don't know which backup of controlfile is really restored. I assume 'from autobackup' would have restored that at /u03/oradata/db7fra/DB7/autobackup/2011_03_15

Regards,
OraKaran

Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499650 is a reply to message #499647] Tue, 15 March 2011 11:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Post output of
list incarnation of database;

Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499651 is a reply to message #499630] Tue, 15 March 2011 11:37 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello Mahesh

here are the details

RMAN> list backup of controlfile;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    11.83M     DISK        00:00:02     15-MAR-11
        BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T123008
        Piece Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_ncsnf_TAG20110315T123008_6qypyrz2_.bkp
  Control File Included: Ckp SCN: 180174       Ckp time: 15-MAR-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    11.83M     DISK        00:00:00     15-MAR-11
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T125045
        Piece Name: /u03/oradata/db7fra/DB7/autobackup/2011_03_15/o1_mf_s_745851045_6qyr55y3_.bkp
  Control File Included: Ckp SCN: 181144       Ckp time: 15-MAR-11

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB7      1748805709       CURRENT 1          15-MAR-11

RMAN>


Regards,
OraKaran
Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499659 is a reply to message #499651] Tue, 15 March 2011 12:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Your set newname command is asking to use /u03.
But RMAN is whining it cannot write to /u04.
Probably you are not telling us the whole story Smile

Quote:
ORA-19504: failed to create file "/u04/oradata/db7/users.dbf"

[Updated on: Tue, 15 March 2011 12:18]

Report message to a moderator

Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499663 is a reply to message #499630] Tue, 15 March 2011 12:17 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello


I don't know till what point (time / scn /logsequence) I will restore

I tried the following and it failed


RMAN> run{
2> set until scn 180174;
3> set newname for datafile 1 to '/u03/oradata/db7/system01.dbf';
4> set newname for datafile 2 to '/u03/oradata/db7/undotbs01.dbf';
5> set newname for datafile 3 to '/u03/oradata/db7/sysaux01.dbf';
6> set newname for datafile 3 to '/u03/oradata/db7/users.dbf';
7> set newname for tempfile 1 to '/u03/oradata/db7/temp.dbf';
8> restore database;
9> switch datafile all;
10> recover database;
11> }

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 15-MAR-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u03/oradata/db7/system01.dbf
restoring datafile 00002 to /u03/oradata/db7/undotbs01.dbf
restoring datafile 00003 to /u03/oradata/db7/users.dbf
restoring datafile 00004 to /u04/oradata/db7/users.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
ORA-19870: error reading backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T123008_6qypyjyt_.bkp
ORA-19504: failed to create file "/u04/oradata/db7/users.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
failover to previous backup

creating datafile fno=1 name=/u03/oradata/db7/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/15/2011 17:14:15
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u04/oradata/db7/system01.dbf'

RMAN>




In case you think the recovery has taken wrong controlfile please let me know and I shall repeat all the exercise since start

Apart from this I could understand why there are 2 backups of controlfile?

Regards,
OraKaran
Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499664 is a reply to message #499663] Tue, 15 March 2011 12:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Sorry. I updated my last post.
Your set newname command is asking to use /u03.
But RMAN is whining it cannot write to /u04.
Probably you are not telling us the whole story Smile


Quote:

ORA-19504: failed to create file "/u04/oradata/db7/users.dbf"

[Updated on: Tue, 15 March 2011 12:30]

Report message to a moderator

Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499673 is a reply to message #499663] Tue, 15 March 2011 12:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Typo in your set commands.
Second one should be datafile 4?
5> set newname for datafile 3 to '/u03/oradata/db7/sysaux01.dbf';
6> set newname for datafile 3 to '/u03/oradata/db7/users.dbf';
Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499781 is a reply to message #499630] Wed, 16 March 2011 03:50 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Superb! Many Thanks Mahesh

Thanks for your time and patience to correct my mistake

After I corrected the error pointed by you (the file number), it went fine. Only here I did not try explicit incomplete recovery and RMAN did the correct thing

RMAN> run{
2> set newname for datafile 1 to '/u03/oradata/db7/system01.dbf';
set newname for datafile 2 to '/u03/oradata/db7/undotbs01.dbf';
3> 4> set newname for datafile 3 to '/u03/oradata/db7/sysaux01.dbf';
5> set newname for datafile 4 to '/u03/oradata/db7/users.dbf';
6> set newname for tempfile 1 to '/u03/oradata/db7/temp.dbf';
7> restore database;
8> switch datafile all;
9> recover database;
10> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 16-MAR-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u03/oradata/db7/system01.dbf
restoring datafile 00002 to /u03/oradata/db7/undotbs01.dbf
restoring datafile 00003 to /u03/oradata/db7/sysaux01.dbf
restoring datafile 00004 to /u03/oradata/db7/users.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_nnndf_TAG20110315T125008_6qyr40pc_.bkp tag=TAG20110315T125008
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 16-MAR-11

datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=745922002 filename=/u03/oradata/db7/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=745922002 filename=/u03/oradata/db7/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=745922002 filename=/u03/oradata/db7/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=745922002 filename=/u03/oradata/db7/users.dbf

Starting recover at 16-MAR-11
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 16 is already on disk as file /u03/oradata/db7fra/DB7/archivelog/2011_03_15/o1_mf_1_16_6qyvpb3w_.arc
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=15
channel ORA_DISK_1: reading from backup piece /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T125043_6qyr54to_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T125043_6qyr54to_.bkp tag=TAG20110315T125043
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=/u03/oradata/db7fra/DB7/archivelog/2011_03_16/o1_mf_1_15_6r0xgrjf_.arc thread=1 sequence=15
channel default: deleting archive log(s)
archive log filename=/u03/oradata/db7fra/DB7/archivelog/2011_03_16/o1_mf_1_15_6r0xgrjf_.arc recid=8 stamp=745922008
archive log filename=/u03/oradata/db7fra/DB7/archivelog/2011_03_15/o1_mf_1_16_6qyvpb3w_.arc thread=1 sequence=16
unable to find archive log
archive log thread=1 sequence=17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/16/2011 08:33:33
RMAN-06054: media recovery requesting unknown log: thread 1 seq 17 lowscn 183058

RMAN> exit


Recovery Manager complete.
oracle@dev-idt.dev.luthor.biz:/u03/oradata/db7fra $sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 16 08:37:15 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter user-name: sys / as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/u04/oradata/db7/redo01a.rdo'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u04/oradata/db7/redo01a.rdo
/u04/oradata/db7/redo01b.rdo
/u04/oradata/db7/redo02a.rdo
/u04/oradata/db7/redo02b.rdo
/u04/oradata/db7/redo03a.rdo
/u04/oradata/db7/redo03b.rdo

6 rows selected.

SQL> alter database rename file '/u04/oradata/db7/redo01a.rdo' to '/u03/oradata/db7/redo01a.rdo';

Database altered.

SQL> alter database rename file '/u04/oradata/db7/redo01b.rdo' to '/u03/oradata/db7/redo01b.rdo';

Database altered.

SQL> alter database rename file '/u04/oradata/db7/redo02a.rdo' to '/u03/oradata/db7/redo02a.rdo';

Database altered.

SQL> alter database rename file '/u04/oradata/db7/redo02b.rdo' to '/u03/oradata/db7/redo02b.rdo';

Database altered.

SQL> alter database rename file '/u04/oradata/db7/redo03a.rdo' to '/u03/oradata/db7/redo03a.rdo';

Database altered.

SQL> alter database rename file '/u04/oradata/db7/redo03b.rdo' to '/u03/oradata/db7/redo03b.rdo';

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL>



Thanks and Regards,
OraKaran
Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499784 is a reply to message #499630] Wed, 16 March 2011 03:58 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
One more update

During restore I mentioned
run{
set newname for datafile 1 to '/u03/oradata/db7/system01.dbf';
......
..
set newname for tempfile 1 to '/u03/oradata/db7/temp.dbf';


However after opening the database with resetlogs, I found

SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
                      *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 101 - see DBWR trace file
ORA-01110: data file 101: '/u04/oradata/db7/temp.dbf'



Can we say the "set newname" does not take effect for tempfile?

Regards,
OraKaran
Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499786 is a reply to message #499784] Wed, 16 March 2011 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Temp files are never backed up and/or restored by RMAN.
You have to create it manually.
ALTER TABLESPACE TEMP ADD TEMPFILE ...

Regards
Michel
Re: Getting error ORA-19870: error reading backup piece during database cloning [message #499791 is a reply to message #499630] Wed, 16 March 2011 04:16 Go to previous message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Thanks Michel

I got it now

Regards,
OraKaran
Previous Topic: Rman Cloning - what am i doing wrong?
Next Topic: RMAN backup performance
Goto Forum:
  


Current Time: Fri Oct 24 11:40:18 CDT 2014

Total time taken to generate the page: 0.08830 seconds