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;
