Tuesday, May 26, 2020

Reread resulted in same corrupt block on file 'spfil.ora' blockno=1

Active database duplication for standby fails with an error specific to 4k sector size. The primary database is Oracle restart with ASM where disks are on 4k emulation mode (512 logical sector and 4k physical sector). The spfile of the primary reside in the ASM disk (19c DB). During the active database duplicate command the primary would copy the spfile (with relavent changes specified by the duplication command) to the standby instance. But this location is not the ASM but $ORACLE_HOME/dbs. However, when the 4k emulation mode is involved this copying spfile from ASM to file system location results in following error on the primary DB rman session.
RMAN> duplicate target database for standby from active database
2> spfile
3> parameter_value_convert 'masterdb','masterdr','MASTERDB','MASTERDR'
set db_name='masterdb'
4> 5> set db_unique_name='masterdr'
6> set fal_server='masterdbtns'
7> set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=masterdr NOREOPEN ALTERNATE=log_archive_dest_2'
8> set log_archive_dest_3='service=masterdbtns LGWR ASYNC NOAFFIRM max_failure=10 reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=masterdb'
9> set local_listener='LISTENER_MASTERDR';

Starting Duplicate Db at 28-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=268 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/opt/app/oracle/product/19.x.0/dbhome_1/dbs/orapwmasterdr'   targetfile
 '+DATA/masterdb/parameterfile/spfile.293.1012128045' auxiliary format
 '/opt/app/oracle/product/19.x.0/dbhome_1/dbs/spfilemasterdr.ora'   ;
   sql clone "alter system set spfile= ''/opt/app/oracle/product/19.x.0/dbhome_1/dbs/spfilemasterdr.ora''";
}
executing Memory Script

Starting backup at 28-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=575 device type=DISK
Finished backup at 28-JUN-19

sql statement: alter system set spfile= ''/opt/app/oracle/product/19.x.0/dbhome_1/dbs/spfilemasterdr.ora''


Reread resulted in same corrupt block on file '/opt/app/oracle/product/19.x.0/dbhome_1/dbs/spfilemasterdr.ora' blockno=1
Reread resulted in same corrupt block on file '/opt/app/oracle/product/19.x.0/dbhome_1/dbs/spfilemasterdr.ora' blockno=1
Reread resulted in same corrupt block on file '/opt/app/oracle/product/19.x.0/dbhome_1/dbs/spfilemasterdr.ora' blockno=1
Reread resulted in same corrupt block on file '/opt/app/oracle/product/19.x.0/dbhome_1/dbs/spfilemasterdr.ora' blockno=1

The error keeps repeating and no going forward from this point.

The solution was to move the DB spfile out of ASM into local disk ($ORACLE_HOME/dbs) and re-run the duplicate command. There were no issue in the subsequent execution.

Related Post
4K Sector, compatible.rdbms and Redo Log File Block Size