I was wondering what are the steps to be done before the restore.
Also what is the high level flow of the process for this.
As per oracle doc:
When Automatic Storage Management (ASM) disk groups are used, an RMAN restore
operation creates new copies of data files only if the full name of a data file, including
the incarnation, does not match with the name of an existing data file.
A fully qualified ASM file name is of the form +diskgroup/dbname/filetype/filetypetag.file.incarnation.
When you first restore the control file and then restore the other database files, the names of the data
files in the control file may not match with the names of the existing data files and therefore the data files are recreated.
Use one of the following methods to ensure that existing data files are not recreated during a restore or duplicate operation:
■ In the control file, use alias names for each data file. The alias must not include the
ASM incarnation number.
■ After restoring the control file and before restoring the other database files, use the
CATALOG command to ensure that the existing data files are cataloged in the
restored control file. Next, use the SWITCH command to make the restored control
file point to the existing data files.
■ Use SET NEWNAME to rename the data files before restoring the data files and after
restoring the control file.
https://docs.oracle.com/cd/E11882_01/backup.112/e10642.pdf
===
Our restore attempt fails with this:
app/oracle/grid/product/19.5/bin/sqlplus
version = 19.0.0.0.0
3872 f20 11/02 11:05:24 2273206 ::GetASMLogDisks() - Log Destination ASM Disk Group Name = FRA01
32391 7e87 11/02 10:44:32 2273206 OraChildProcess::SetPostForkParam() - Parent path = /app/oracle/database/product/19.5/bin/sqlplus
3872 f20 11/02 11:05:33 2273206 ClOraSnapAgent::CheckSqlOutputError() - SQL script execution failed with an error [ORA-01078: failure in processing system parameters]. Please check ClOraAgent.log.
3872 f20 11/02 11:05:33 2273206 ClOraSnapAgent::CheckSqlOutputError() - m_jobObject->setPendingCause(SQL script execution failed with an error [ORA-01078: failure in processing system parameters]. Please check ClOraAgent.log.)...
3872 f20 11/02 11:05:33 2273206 ClOraSnapAgent::StoreDbCfg() - Creating spfile/pfile failed, Error=
====
Executing sql [shutdown immediate;
exit;
]
Executing sql [select status from v$instance;
quit;
]
Executing sql [startup nomount;
quit;
]
Rman Script:
[run
{
allocate channel ch1 type disk format '%U';
restore controlfile from '+LS222732361/LDBOMPRD/ARCHIVELOG/backup.ctl.galaxy';
}
exit;
]
Rman Log:[
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 2 11:49:40 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN>
connected to target database: LDBOMPRD (not mounted)
using target database control file instead of recovery catalog
RMAN> 2> 3> 4> 5>
allocated channel: ch1
channel ch1: SID=1138 device type=DISK
Starting restore at Nov 02 2021 11:49:41
channel ch1: copied control file copy
output file name=+DATA01/LDBOMPRD/CONTROLFILE/current.257.1055608055
output file name=+FRA01/LDBOMPRD/CONTROLFILE/current.256.1055608057
Finished restore at Nov 02 2021 11:49:44
released channel: ch1
RMAN>
Recovery Manager complete.
]
Executing sql [select status from v$instance;
quit;
]
Executing sql [alter database mount;
quit;
]
Executing SQL script [ SET SERVEROUTPUT ON;
DECLARE
archivelog_dest VARCHAR2(512);
sqlStr VARCHAR2(1024);
BEGIN
SELECT name into archivelog_dest FROM V$PARAMETER WHERE lower(NAME) like 'log_archive_dest%'
AND VALUE IS NOT NULL and upper(VALUE) LIKE '%USE_DB_RECOVERY_FILE_DEST%';
sqlStr := 'ALTER SYSTEM SET ' || archivelog_dest || '=''LOCATION=+FRA01/ldbomprd/archivelog/''';
EXECUTE IMMEDIATE sqlStr;
DBMS_OUTPUT.PUT_LINE( 'ALTER SYSTEM SET ' || archivelog_dest || '=''LOCATION=USE_DB_RECOVERY_FILE_DEST'';');
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO MANY ARCHIVELOG DESTINATIONS POINTING TO FRA NOT EXPECTED');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('LOG ARCHIVE DESTS pointing to FRA NOTFOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( 'SQLCODE=' || SQLCODE || ' ERROR=' || SQLERRM );
END;
/
ALTER DATABASE FLASHBACK OFF;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='';
exit;
]
Rman Script:
[run
{
allocate channel ch1 type disk format '%U';
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/undotbs1.281.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/sysaux.264.1055608119' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/system.262.1055608115' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/undotbs1.266.1055608119' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/undotbs1.267.1055608119' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/undotbs1.268.1055608121' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/undotbs1.269.1055608123' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/users.276.1055608135' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/sysaux.283.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/system.282.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/undotbs1.278.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/undotbs1.279.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/undotbs1.280.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/users.294.1084532847' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/sysaux.265.1055608119' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/system.263.1055608117' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/undotbs1.270.1055608123' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/undotbs1.271.1055608123' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/undotbs1.272.1055608123' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/undotbs1.273.1055608125' tag 'SNAP_2_2273236' ;
}
exit;
]
Rman Log:[
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 2 11:51:07 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN>
connected to target database: LDBOMPRD (DBID=2749196020, not open)
using target database control file instead of recovery catalog
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24>
allocated channel: ch1
channel ch1: SID=1990 device type=DISK
Starting implicit crosscheck backup at Nov 02 2021 11:51:07
Crosschecked 12 objects
Finished implicit crosscheck backup at Nov 02 2021 11:51:08
Starting implicit crosscheck copy at Nov 02 2021 11:51:08
Finished implicit crosscheck copy at Nov 02 2021 11:51:08
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +FRA01/LDBOMPRD/ARCHIVELOG/backup.ctl.galaxy
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of catalog command on ch1 channel at 11/02/2021 11:51:10
ORA-19564: error occurred writing 8192 bytes at block number 1
ORA-15081: failed to submit an I/O operation to a disk
RMAN>
Recovery Manager complete.
]
Rman Script:
[run
{
allocate channel ch1 type disk format '%U';
change copy of database tag 'SNAP_2_2273236' uncatalog ;
}
exit;
]
Rman Log:[
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 2 11:51:15 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN>
connected to target database: LDBOMPRD (DBID=2749196020, not open)
using target database control file instead of recovery catalog
RMAN> 2> 3> 4> 5>
allocated channel: ch1
channel ch1: SID=1990 device type=DISK
specification does not match any datafile copy in the repository
released channel: ch1
RMAN>
Recovery Manager complete.
]
Rman Script:
[run
{
allocate channel ch1 type disk format '%U';
change copy of controlfile like '+LS222732361/LDBOMPRD/ARCHIVELOG/%' uncatalog;
change archivelog like '+LS222732361/LDBOMPRD/ARCHIVELOG/%' uncatalog;
}
exit;
]
Rman Log:[
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 2 11:51:22 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN>
connected to target database: LDBOMPRD (DBID=2749196020, not open)
using target database control file instead of recovery catalog
RMAN> 2> 3> 4> 5> 6>
allocated channel: ch1
channel ch1: SID=1990 device type=DISK
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
released channel: ch1
RMAN>
Recovery Manager complete.
]
Executing sql [shutdown immediate;
exit;
]
Executing sql [shutdown immediate;
exit;
]
Executing sql [select status from v$instance;
quit;
]
Executing sql [startup nomount;
quit;
]
Rman Script:
[run
{
allocate channel ch1 type disk format '%U';
restore controlfile from '+LS222732361/LDBOMPRD/ARCHIVELOG/backup.ctl.galaxy';
}
exit;
]
Rman Log:[
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 2 12:11:41 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN>
connected to target database: LDBOMPRD (not mounted)
using target database control file instead of recovery catalog
RMAN> 2> 3> 4> 5>
allocated channel: ch1
channel ch1: SID=1138 device type=DISK
Starting restore at Nov 02 2021 12:11:41
channel ch1: copied control file copy
output file name=+DATA01/LDBOMPRD/CONTROLFILE/current.257.1055608055
output file name=+FRA01/LDBOMPRD/CONTROLFILE/current.256.1055608057
Finished restore at Nov 02 2021 12:11:45
released channel: ch1
RMAN>
Recovery Manager complete.
]
Executing sql [select status from v$instance;
quit;
]
Executing sql [alter database mount;
quit;
]
Executing SQL script [ SET SERVEROUTPUT ON;
DECLARE
archivelog_dest VARCHAR2(512);
sqlStr VARCHAR2(1024);
BEGIN
SELECT name into archivelog_dest FROM V$PARAMETER WHERE lower(NAME) like 'log_archive_dest%'
AND VALUE IS NOT NULL and upper(VALUE) LIKE '%USE_DB_RECOVERY_FILE_DEST%';
sqlStr := 'ALTER SYSTEM SET ' || archivelog_dest || '=''LOCATION=+FRA01/ldbomprd/archivelog/''';
EXECUTE IMMEDIATE sqlStr;
DBMS_OUTPUT.PUT_LINE( 'ALTER SYSTEM SET ' || archivelog_dest || '=''LOCATION=USE_DB_RECOVERY_FILE_DEST'';');
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO MANY ARCHIVELOG DESTINATIONS POINTING TO FRA NOT EXPECTED');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('LOG ARCHIVE DESTS pointing to FRA NOTFOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( 'SQLCODE=' || SQLCODE || ' ERROR=' || SQLERRM );
END;
/
ALTER DATABASE FLASHBACK OFF;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='';
exit;
]
Rman Script:
[run
{
allocate channel ch1 type disk format '%U';
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/undotbs1.281.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/sysaux.264.1055608119' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/system.262.1055608115' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/undotbs1.266.1055608119' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/undotbs1.267.1055608119' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/undotbs1.268.1055608121' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/undotbs1.269.1055608123' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/DATAFILE/users.276.1055608135' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/sysaux.283.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/system.282.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/undotbs1.278.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/undotbs1.279.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/undotbs1.280.1055610579' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34B34CCE8EA64D0E0536E1D1E0AC348/DATAFILE/users.294.1084532847' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/sysaux.265.1055608119' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/system.263.1055608117' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/undotbs1.270.1055608123' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/undotbs1.271.1055608123' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/undotbs1.272.1055608123' tag 'SNAP_2_2273236' ;
catalog datafilecopy '+DS222732360/LDBOMPRD/B34AA201F92725C2E0536E1D1E0A0CA0/DATAFILE/undotbs1.273.1055608125' tag 'SNAP_2_2273236' ;
}
exit;
]
Rman Log:[
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 2 12:13:07 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN>
connected to target database: LDBOMPRD (DBID=2749196020, not open)
using target database control file instead of recovery catalog
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24>
allocated channel: ch1
channel ch1: SID=1990 device type=DISK
Starting implicit crosscheck backup at Nov 02 2021 12:13:08
Crosschecked 12 objects
Finished implicit crosscheck backup at Nov 02 2021 12:13:08
Starting implicit crosscheck copy at Nov 02 2021 12:13:08
Finished implicit crosscheck copy at Nov 02 2021 12:13:08
searching for all files in the recovery area
cataloging files...
no files cataloged
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of catalog command on ch1 channel at 11/02/2021 12:13:10
ORA-19564: error occurred writing 8192 bytes at block number 1
ORA-15081: failed to submit an I/O operation to a disk
RMAN>
Recovery Manager complete.
]
Rman Script:
[run
{
allocate channel ch1 type disk format '%U';
change copy of database tag 'SNAP_2_2273236' uncatalog ;
}
exit;
]
Rman Log:[
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 2 12:13:15 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN>
connected to target database: LDBOMPRD (DBID=2749196020, not open)
using target database control file instead of recovery catalog
RMAN> 2> 3> 4> 5>
allocated channel: ch1
channel ch1: SID=1990 device type=DISK
specification does not match any datafile copy in the repository
released channel: ch1
RMAN>
Recovery Manager complete.
]
Rman Script:
[run
{
allocate channel ch1 type disk format '%U';
change copy of controlfile like '+LS222732361/LDBOMPRD/ARCHIVELOG/%' uncatalog;
change archivelog like '+LS222732361/LDBOMPRD/ARCHIVELOG/%' uncatalog;
}
exit;
]
Rman Log:[
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 2 12:13:21 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN>
connected to target database: LDBOMPRD (DBID=2749196020, not open)
using target database control file instead of recovery catalog
RMAN> 2> 3> 4> 5> 6>
allocated channel: ch1
channel ch1: SID=1990 device type=DISK
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
released channel: ch1
RMAN>
Recovery Manager complete.
]
target database control file instead of recovery catalog
RMAN> 2> 3> 4> 5> 6>
allocated channel: ch1
channel ch1: SID=1990 device type=DISK
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
released channel: ch1
RMAN>
Recovery Manager complete.
]
