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;

AWS Application Migration Service Error : Failed Installing the AWS Replication Agent

AWS Application Migration Service Error : Failed Installing the AWS Replication Agent

[root@ip ~]# python3 aws-replication-installer-init.py

The installation of the AWS Replication Agent has started.

AWS Region Name: XXXXXXXXXXXXXXX

AWS Access Key ID: XXXXXXXXXXXXXXX

AWS Secret Access Key:

Identifying volumes for replication.

Choose the disks you want to replicate. Your disks are: /dev/xvda

To replicate some of the disks, type the path of the disks, separated with a comma (for example, /dev/sda,/dev/sdb). To replicate all disks, press Enter:

Identified volume for replication: /dev/xvda of size 30 GiB

All volumes for replication were successfully identified.

Downloading the AWS Replication Agent onto the source server... Finished.

Installing the AWS Replication Agent onto the source server...

Error: Failed Installing the AWS Replication Agent

Installation failed.

Learn more about installation issues in our documentation at https://docs.aws.amazon.com/mgn/latest/ug/Error-Installtion-Failed.html

[root@ip ~]# 

Solutions: Install package elfutils.* ,elfutils-libelf-devel 

[root@ip ~]# python3 aws-replication-installer-init.py

The installation of the AWS Replication Agent has started.

AWS Region Name: XXXXXXXXXXXXXXX

AWS Access Key ID: XXXXXXXXXXXXXXX

AWS Secret Access Key:

Identifying volumes for replication.

Choose the disks you want to replicate. Your disks are: /dev/xvda

To replicate some of the disks, type the path of the disks, separated with a comma (for example, /dev/sda,/dev/sdb). To replicate all disks, press Enter:

Identified volume for replication: /dev/xvda of size 30 GiB

All volumes for replication were successfully identified.

Downloading the AWS Replication Agent onto the source server... Finished.

Installing the AWS Replication Agent onto the source server... Finished.

Syncing the source server with the Application Migration Service Console... Finished.

The following is the source server ID: XXXXXXXXXXXXXX

The AWS Replication Agent was successfully installed.

[root@ip ~]#

Convert a Snapshot Standby Back to a Physical Standby using DGMGRL

Converting a Snapshot Standby Back to a Physical Standby using DGMGRL 

DGMGRL> convert database india_stby to physical standby;

Converting database "india_stby" to a Physical Standby database, please wait...

Operation requires shut down of instance "india_stby" on database "india_stby"

Shutting down instance "india_stby"...

Connected to "india_stby"

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires start up of instance "india_stby" on database "india_stby"

Starting instance "india_stby"...

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

ORACLE instance started.

Database mounted.

Connected to "india_stby"

Continuing to convert database "india_stby" ...

Database "india_stby" converted successfully

DGMGRL>

DGMGRL> sql 'alter system switch logfile';

Succeeded.

DGMGRL> show configuration

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india      - Primary database

    india_stby - Physical standby database

      Warning: ORA-16854: apply lag could not be determined

Fast-Start Failover: DISABLED

Configuration Status:

WARNING   (status updated 45 seconds ago)

DGMGRL>

DGMGRL> show configuration;

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india      - Primary database

    india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 21 seconds ago)

DGMGRL>

on standby database table hr.abc_snap table does not exist which we created when database role was snapshot standby

===================================================================

[oracle@ip-10-0-1-37 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 26 11:19:33 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from hr.abc_snap;

select * from hr.abc_snap

                 *

ERROR at line 1:

ORA-00942: table or view does not exist

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

NAME      OPEN_MODE            DATABASE_ROLE

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

INDIA     READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>

Convert a Physical Standby to a Snapshot Standby using DGMGRL

 

Convert a Physical Standby to a Snapshot Standby using DGMGRL

[oracle@ip-10-0-1-12 ~]$ dgmgrl

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Wed May 26 11:07:46 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/@india

Password:

Connected to "india"

Connected as SYSDBA.

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL> show configuration;

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india      - Primary database

    india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 43 seconds ago)

DGMGRL>

DGMGRL>

DGMGRL> validate database india;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:

    india:  Off

  Managed by Clusterware:

    india:  NO

    Warning: Ensure primary database's StaticConnectIdentifier property

    is configured properly so that the primary database can be restarted

    by DGMGRL after switchover

DGMGRL>

DGMGRL> validate database india_stby;

  Database Role:     Physical standby database

  Primary Database:  india

  Ready for Switchover:  Yes

  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:

    india     :  Off

    india_stby:  On

  Managed by Clusterware:

    india     :  NO

    india_stby:  NO

    Warning: Ensure primary database's StaticConnectIdentifier property

    is configured properly so that the primary database can be restarted

    by DGMGRL after switchover

  Current Log File Groups Configuration:

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status

              (india)                 (india_stby)

    1         3                       3                       Insufficient SRLs

  Future Log File Groups Configuration:

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status

              (india_stby)            (india)

    1         3                       2                       Insufficient SRLs

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL> convert database india_stby to snapshot standby;

Converting database "india_stby" to a Snapshot Standby database, please wait...

Database "india_stby" converted successfully

DGMGRL>

On Standby database 

====================

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

NAME      OPEN_MODE            DATABASE_ROLE

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

INDIA     READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> /

select name ,open_mode,database_role from v$database

*

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 5938

Session ID: 272 Serial number: 59433

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@ip-10-0-1-37 ~]$

[oracle@ip-10-0-1-37 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 26 11:11:02 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

NAME      OPEN_MODE            DATABASE_ROLE

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

INDIA     READ WRITE           SNAPSHOT STANDBY

SQL>

SQL>

SQL>

SQL> create table hr.abc_snap as select * from hr.abc;

Table created.

SQL>

SQL> select * from hr.abc_snap;

        ID NAME

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

         2 From standby after primary

         1 A

         3 from primary after convert

SQL>