Roles assigned to Primary and Standby Databases

How to check the assigned roles to Primary and Standby Databases








[oracle@uhesse ~]$ sqlplus sys/oracle@physt as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 16 10:25:51 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role from v$database;

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

SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> select database_role from v$database;

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

primary database convert to physical standby

Accidentally primary database convert to physical standby  Don't worry there is a solution






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

Total System Global Area  313860096 bytes
Fixed Size                  1299624 bytes
Variable Size             285215576 bytes
Database Buffers           20971520 bytes
Redo Buffers                6373376 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.
SQL> alter database convert to primary database;
alter database convert to primary database
                          *
ERROR at line 1:
ORA-00922: missing or invalid option

SQL> alter database activate standby database;

Database altered.

SQL> alter database open;

Database altered.

SQL> select database_role from v$database;

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

ORA-01665: control file is not a standby control file

                                    ORA-01665: control file is not a standby control file




So, the quick solution for ORA-01665 will be:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Lets see the small example:

SQL> STARTUP MOUNT

ORACLE instance started.

…
Database mounted.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

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;

Database altered.

SQL> STARTUP MOUNT

ORACLE instance started.

…
Database mounted.

SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

So, now I have control file for Physical Standby database and I’m able to start Managed Recovery and I think that it is much easier/quicker than using standard procedure or making new standby control file on primary, copying it to standby host, restoring, etc.