How to Fix ORA-10458 & ORA-01196 in Standby 11g (Roll Forward a Physical Standby Database)

This post presents a quick and efficient alternative to rebuilding a physical standby database in Oracle Database 11g Release 2 by using RMAN incremental backups to roll forward and resynchronize a physical standby database with the primary database or Fix ORA-10458 & ORA-01196. Using the RMAN BACKUP INCREMENTAL FROM SCN command, you can create an incremental backup on the primary database that starts at the standby database’s current SCN, which can then be used to roll the standby database forward in time.

In 12c, this procedure has been dramatically simplified. Now you can use the RECOVER … FROM SERVICE command to synchronize the physical standby database with the primary database.

Click here to see full document with command output

Standby

SQL>  alter database open;
 alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: 'K:\UCASII\ORADATA_2\O1_MF_SYSTEM_GMYLZ22B_.DBF'

Step 1: Take note of SCN in Standby: On the physical standby database, find the SCN which will be used for the incremental backup at the primary database, as the backup must be created from this SCN forward.

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
13759890709

Step 2. Take note of database file system location in Standby: Capture records of all physical files from standby database.

SQL> set lines 300
SQL> set pages 300
SQL> col name for a65
SQL> select name from v$datafile;
SQL> select name from v$tempfile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;

OR

select name from v$controlfile
union
select file_name from dba_data_files
union
select file_name from  dba_temp_files
union
select member from v$logfile;

Step 3. Take Incremental backup from Primary: Using RMAN, connect to the primary database and create an incremental backup from the SCN derived in the Step 1.

RMAN> run {
allocate channel ch1 device type disk;
allocate channel ch2  device type disk;
backup as compressed backupset incremental from scn 13759890709 database format '/u01/DB_PUMP/ForStandby_%U' tag 'FORSTANDBY';
release channel ch1;
release channel ch2;
}

Step 4. Transfer backup to Standby Database: Transfer all backup sets created on the primary system to the physical standby machine

D:\UCAS_Backup>
D:\UCAS_Backup>pscp.exe oracle@test-machine02:/u01/DB_PUMP/ForStandby* .
[email protected]'s password:
ForStandby_efvs9b6g_1_1   | 5275136 kB | 16640.8 kB/s | ETA: 00:00:00 | 100%
ForStandby_egvs9bo5_1_1   | 1360 kB | 1360.0 kB/s | ETA: 00:00:00 | 100%
D:\UCAS_Backup>
D:\UCAS_Backup>

Step 5: Catalog new backup in Standby DB and apply incremental backup: After copying the incremental backup pieces to the physical standby machine, connect to the physical standby database using RMAN and catalog those incremental backup pieces. Then recover the standby database with the cataloged incremental backup pieces.

RMAN> catalog start with 'D:\UCAS_Backup\ForStandby*';

RMAN> run {
allocate channel ch1 device type disk;
allocate channel ch2  device type disk;
recover database noredo;
release channel ch1;
release channel ch1;
}

Step 6. Create a standby control file backup: Connect to the primary database using RMAN and create a standby control file backup.

run {
allocate channel ch1 device type disk;
backup current controlfile for standby format '/u01/DB_PUMP/ForStandbyCTRL.bck';
release channel ch1;
}

Step 7. Transfer Standby controlfile backup to Standby Database: Transfer standby controlfile backup sets created on the primary system to the physical standby machine

D:\UCAS_Backup>
D:\UCAS_Backup>pscp.exe [email protected]:/u01/DB_PUMP/ForStandbyCTRL.bck .
[email protected]'s password:
ForStandbyCTRL.bck        | 12992 kB | 12992.0 kB/s | ETA: 00:00:00 | 100%
D:\UCAS_Backup>

Step 8. Shut down the physical standby database and startup nomount: Stop and start standby database in nomount state.

SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2293232 bytes
Variable Size            1660944912 bytes
Database Buffers         4731174912 bytes
Redo Buffers               19267584 bytes

Step 9. Connect to the physical standby database using RMAN and restore the standby control file: Restore controlfile on the standby database.

C:\Users\jkhan>rman target sys
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Apr 14 14:38:17 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
target database Password:
connected to target database: UCASII (not mounted)
RMAN>
RMAN>  restore standby controlfile from 'D:\UCAS_Backup\ForStandbyCTRL.bck';
Starting restore at 14-APR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1057 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=F:\UCASII\ORADATA_1\CONTROL01.CTL
output file name=G:\UCASII\ORADATA_1\CONTROL02.CTL
Finished restore at 14-APR-21

RMAN>

Step 10. Shut down the physical standby database again and startup mount: Restart standby database in mount state.

SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2293232 bytes
Variable Size            1660944912 bytes
Database Buffers         4731174912 bytes
Redo Buffers               19267584 bytes
Database mounted.

Step 11. Catalog all standby data files: If the primary and standby database data file directories are identical, skip this step. If the primary and standby database data file directories are different, then in RMAN, connect to the physical standby database, catalog all standby data files, and switch the standby database to use the just-cataloged data files. After all standby database datafiles copies have been cataloged, switch the standby database to use the just-cataloged data files.

C:\Users\jkhan>rman target sys
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Apr 14 14:38:17 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
target database Password:
connected to target database: UCASII (not mounted)
RMAN>
RMAN> catalog start with 'K:\UCASII\ORADATA_2\';
RMAN> catalog start with 'N:\UCASII\ORADATA_2\';
RMAN> switch database to copy;

Step 12. Clear Standby Logfiles: The same situation exists with the redo log files that existed with the data file directories. If the primary and standby database redo log directories are identical, skip this step. Otherwise, on the standby database, use an OS utility to remove all online and standby redo logs from the standby directories. Without removing all online and standby redo logs and you go to Step 13 you may get below error ORA-00340 after MRP started. Click here to understand to Add & Drop Standby Redolog Files on Standby (Dataguard) in Oracle.

M:\FLASH_RECOVERY_AREA\UCASIIDR\ONLINELOG> del *
M:\FLASH_RECOVERY_AREA\UCASIIDR\ONLINELOG>

SQL> set lines 300
col destination for a40
col error for a20
select inst_id, dest_id "ID",destination,status,error,target,
schedule,process,mountid  mid
from gv$archive_dest
where dest_id < 4
order by dest_id;
SQL> SQL> SQL>   2    3    4    5
   INST_ID         ID DESTINATION                              STATUS    ERROR                TARGET  SCHEDULE PROCESS           MID
---------- ---------- ---------------------------------------- --------- -------------------- ------- -------- ---------- ----------
         1          1 USE_DB_RECOVERY_FILE_DEST                VALID                          PRIMARY ACTIVE   ARCH                0
         1          2 ucasiidr                                 ERROR     ORA-00340: IO error  STANDBY ACTIVE   LGWR                0
                                                                         processing online
                                                                         log  of thread

         1          3                                          INACTIVE                       PRIMARY INACTIVE ARCH                0

SQL> /

Step 13. On the standby database, clear all of the online redo log and standby redo log groups.

SQL> select distinct group#, type from v$logfile order by group#;

    GROUP# TYPE
---------- -------
         1 ONLINE
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 STANDBY
         7 STANDBY
         8 STANDBY
         9 STANDBY
        10 STANDBY
        11 STANDBY

11 rows selected.

SQL>

SQL> alter database clear logfile group 1;
Database altered.

SQL> alter database clear logfile group 2;
Database altered.

SQL> alter database clear logfile group 3;
Database altered.

SQL> alter database clear logfile group 4;
Database altered.

SQL> alter database clear logfile group 5;
Database altered.

SQL> alter database clear logfile group 6;
Database altered.

SQL> alter database clear logfile group 7;
Database altered.

SQL> alter database clear logfile group 8;
Database altered.

SQL> alter database clear logfile group 9;
Database altered.

SQL> alter database clear logfile group 10;
Database altered.

SQL> alter database clear logfile group 11;
Database altered.

Step 14. Enable Flashback: After successful completion of clearing all standby redo log groups, re-enable Flashback database on the physical standby database. (Applicable if you are using Flashback Feature).

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

Step 15. Finally, on the physical standby database, restart the managed recovery process (MRP).

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> set lines 200
SQL> set pages 200
SQL> select inst_id,process,status,client_process,THREAD#,sequence#,block#,active_agents,known_agents from gv$managed_standby where PROCESS like 'MRP%' or client_process like 'LGWR

   INST_ID PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------
         1 RFS       IDLE         LGWR              1      15287       2428             0            0
         1 MRP0      APPLYING_LOG N/A               1      15286          0            33           33

SQL> /

   INST_ID PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------
         1 RFS       IDLE         LGWR              1      15287       2429             0            0
         1 MRP0      APPLYING_LOG N/A               1      15286          1            33           33

SQL> /

   INST_ID PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------
         1 RFS       IDLE         LGWR              1      15288          7             0            0
         1 MRP0      APPLYING_LOG N/A               1      15288          6            33           33

SQL>

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

How to Fix ORA-10458 & ORA-01196 in Standby 11g (Roll Forward a Physical Standby Database)

Related Articles