Monday, 18 November 2013

ORA-01153: an incompatible media recovery is active standby database oracle



ORA-01153: an incompatible media recovery is active

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT FROM SESSION;  2
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
*
ERROR at line 1:
ORA-01665: control file is not a standby control file


SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             331352224 bytes
Database Buffers          197132288 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted


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

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             331352224 bytes
Database Buffers          197132288 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT FROM SESSION;  2
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#
--------- -------- ---------- ---------- ----------
MRP0      N/A               1         26          0

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

no rows selected

SQL> SELECT open_mode,database_role FROM v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY            PHYSICAL STANDBY

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

no rows selected

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL>  SELECT open_mode,database_role FROM v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY

3 comments:

  1. Great work around , works very well Shiven. Thanks.

    ReplyDelete
  2. what should we do if we see a row of RFS process show up when we do select process, ...

    ReplyDelete