Oracle standby database Error ORA-10458: standby database requires recovery,ORA-01196,ORA-01110

 

Oracle database Error ORA-10458: standby database requires recovery

ORA-01196: file 1 is inconsistent due to a failed media recovery session

ORA-01110: data file 1

SQL> set numwidth 30;

SQL> set pagesize 50000;

SQL> alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';

select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;

Session altered.

SQL>

STATUS              CHECKPOINT_CHANGE# CHECKPOINT_TIME                   RESETLOGS_CHANGE# RESETLOGS_TIME                             COUNT(*) FUZ

------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------ ---

ONLINE                         2472666 26-MAY-2021 11:19:09                        1408558 29-APR-2021 11:19:37                              4 YES

SQL> select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

                MIN(FHRBA_SEQ)                 MAX(FHRBA_SEQ)

------------------------------ ------------------------------

                            63                             63

SQL>

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

 SEQUENCE# APPLIED

---------- ---------

         1 NO

         2 NO

        20 YES

        21 YES

        19 YES

        22 YES

        23 YES

        24 YES

        25 YES

        26 YES

        27 YES

        28 YES

        29 YES

        30 YES

        31 YES

        32 YES

        33 YES

        34 YES

        36 YES

        35 YES

        38 YES

        40 YES

        41 YES

        39 YES

        37 YES

        42 YES

        43 YES

        44 YES

        45 YES

        46 YES

        47 YES

        47 YES

        46 YES

        48 YES

        48 YES

        49 YES

        49 NO

        50 NO

        50 YES

        52 YES

        51 YES

        53 YES

        54 YES

        55 YES

        56 YES

        57 YES

        58 YES

        59 YES

        60 YES

        61 YES

        62 YES

SQL>

SQL> alter database open read only;

alter database open read only

*

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: '/u01/app/oracle/oradata/india_stby/system01.dbf'

SQL>

SQL>

SQL> recover standby database;

ORA-00279: change 2472666 generated at 05/26/2021 11:19:09 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fra/INDIA_STBY/archivelog/2021_05_27/o1_mf_1_63_%u_.arc

ORA-00280: change 2472666 for thread 1 is in sequence #63

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01196: file 1 is inconsistent due to a failed media recovery session

ORA-01110: data file 1: '/u01/app/oracle/oradata/india_stby/system01.dbf'

ORA-01112: media recovery not started

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from hr.abc;

        ID NAME

---------- --------------------------------------------------

         2 From standby after primary

         1 A

         3 from primary after convert

SQL> select name ,open_mode ,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

INDIA     READ ONLY            PHYSICAL STANDBY

SQL>

SQL>

SQL>

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 1048576000 bytes

Fixed Size                  8628640 bytes

Variable Size             369100384 bytes

Database Buffers          662700032 bytes

Redo Buffers                8146944 bytes

Database mounted.

Database opened.

SQL> select name ,open_mode ,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

INDIA     READ ONLY            PHYSICAL STANDBY

We can open database in read write mode using below command and this is also called manual failover 

SQL> alter database activate standby database;