Question

Restore an oracle ASM database with intellisnap on original location

  • 4 November 2021
  • 5 replies
  • 90 views

Userlevel 2
Badge +7

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.
]
 


5 replies

Userlevel 2
Badge +7

note that we followed these steps:https://documentation.commvault.com/11.24/expert/35482_restoring_data_from_snapshot.html

Restore by Using RMAN

Use the following steps to restore data from a snapshot using RMAN scripts. Note that logs are not restored using RMAN.

  1. From the CommCell Browser, go to Client Computers > client > Oracle.

  2. Right-click the instance, then go to All Tasks > Browse and Restore.

    The Browse and Restore Options dialog box appears.

  3. Select Latest Backup, and then click the View Content button.

  4. Optional: Use non-default browse and restore options. For more information, see Browse and Restore.

  5. Select the database, and then click the Recover All Selected button.

    The Oracle Restore Options dialog box appears.

  6. Depending on the type of restore to be run, click Restore in place or Restore out of place, and then click Next.

    The Oracle In Place Restore Options or Oracle Out of Place Restore Options dialog box appears.

  7. Click Advanced.

    The Advanced Restore Options dialog box appears.

  8. On the General tab, click Use RMAN Restore.

  9. Click OK to close the Advanced Restore Options dialog box.

  10. Click OK to close the Restore Options dialog box and start the restore.

Userlevel 2
Badge +7

https://www.purestorage.com/content/dam/pdf/en/white-papers/protected/wp-oracle-backup-recovery-with-commvault-to-flashblade.pdf uses this option:

Set the database to the correct mode defined in the table, or select the Switch Database mode for Restore check box (Options tab), to set the Data Protection Suite software to automatically move the database to the correct mode before the restore.

Control File in the Restore Required Database Mode
Control file is included in the restore NOMOUNT
Control file not included in the restore MOUNT
Userlevel 2
Badge +7

@Mike Struening Any ideas?

Userlevel 2
Badge +7

also have a support case for this 211102-267 @Mike Struening 

Userlevel 7
Badge +23

Looks like the incident was already closed:

On session reviewed the previous error and seems to be related to user permission
Checked the commvault directories for user / group configuration.
It was configured as root: simpana and 'oracle' was owner for oracle binaries with oinstall as primary group.
executed cvpkgchg to change ownership to root:oinstall and execute new restore
Restore job , 2281521 was completed successfully
You are going to perform few more test and will update the case if you have any query.

I can’t take any credit on this one :joy:

You good to mark it as closed on this end?  No rush if you want to wait a bit!

Reply