Duplicate复制数据库并创建物理StandBy（pfile版本）「建议收藏」

set SQLPROMPT Primary>

代码100分

代码100分set SQLPROMPT StandBy>

Primary>archive log list
Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Current log sequence 7 Primary>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. Primary>startup mount ORACLE instance started. Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo Buffers 16568320 bytes Database mounted. Primary>alter database archivelog; Database altered. Primary>alter database open; Database altered. Primary>archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7 Primary>

代码100分Primary>select force_logging from v$database;

FOR
---
NO Primary>alter database force logging; Database altered.

Primary>col name for a30
Primary>col value for a100 Primary>select name ,value from v$parameter where name in ("audit_file_dest","background_dump_dest","control_files","core_dump_dest","user_dump_dest") ORDER BY name ASC; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------
audit_file_dest /usr/oracle/app/admin/orcl/adump background_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/trace control_files /usr/oracle/app/oradata/orcl/control01.ctl, /usr/oracle/app/flash_recovery_area/orcl/control02.ctl core_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/cdump user_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/trace

[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/admin/orcl/adump
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace [oracle@oracledb ~]$ mkdir -p /usr/oracle/app/oradata/orcl [oracle@oracledb ~]$ mkdir -p /usr/oracle/app/flash_recovery_area/orcl [oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/ [oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace

[oracle@oracledb dbs]$ scp orapworcl 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbs
oracle@172.17.22.17"s password: 
orapworcl                                     100% 1536     1.5KB/s   00:00    
[oracle@oracledb dbs]$

orapwd FILE=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl password=wangshengzhuang entries=30;

Primary>create pfile from spfile;

File created.

orcl.__db_cache_size=1476395008
orcl.__java_pool_size=16777216 orcl.__large_pool_size=16777216 orcl.__oracle_base="/usr/oracle/app"#ORACLE_BASE set from environment orcl.__pga_aggregate_target=1325400064 orcl.__sga_target=1979711488 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=436207616 orcl.__streams_pool_size=0 *.audit_file_dest="/usr/oracle/app/admin/orcl/adump" *.audit_trail="db" *.compatible="11.2.0.0.0" *.control_files="/usr/oracle/app/oradata/orcl/control01.ctl","/usr/oracle/app/flash_recovery_area/orcl/control02.ctl" *.db_block_size=8192 *.db_domain="" *.db_name="orcl" *.db_recovery_file_dest="/usr/oracle/app/flash_recovery_area" *.db_recovery_file_dest_size=4070572032 *.diagnostic_dest="/usr/oracle/app" *.dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)" *.memory_target=3299868672 *.nls_language="SIMPLIFIED CHINESE" *.nls_territory="CHINA" *.open_cursors=300 *.processes=150 *.remote_login_passwordfile="EXCLUSIVE" *.undo_tablespace="UNDOTBS1" *.DB_UNIQUE_NAME=db_primary *.LOG_ARCHIVE_CONFIG="DG_CONFIG=(db_primary,db_standby)" *.LOG_ARCHIVE_DEST_2="SERVICE=tns_standby ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby" #arch表示同步已归档的日志 *.LOG_ARCHIVE_DEST_STATE_2=DEFER #表示归档目的地暂时不可用 *.FAL_SERVER=tns_standby *.FAL_CLIENT=tns_primary *.STANDBY_FILE_MANAGEMENT=AUTO

Primary>create spfile from pfile;

File created. Primary>startup ORACLE instance started. Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo Buffers 16568320 bytes Database mounted. Database opened. Primary>show parameter fal NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ fal_client string tns_primary #tns_primary为主服务器的网络服务名 fal_server string tns_standby 

[oracle@oracledb dbs]$ pwd
/usr/oracle/app/product/11.2.0/dbhome_1/dbs [oracle@oracledb dbs]$ scp initorcl.ora 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbs The authenticity of host "172.17.22.17 (172.17.22.17)" can"t be established. RSA key fingerprint is 72:28:f5:f9:9c:f8:49:23:48:6d:9d:d4:0e:0c:89:71. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added "172.17.22.17" (RSA) to the list of known hosts. oracle@172.17.22.17"s password: initorcl.ora 100% 1291 1.3KB/s 00:00 [oracle@oracledb dbs]$

orcl.__db_cache_size=1476395008
orcl.__java_pool_size=16777216 orcl.__large_pool_size=16777216 orcl.__oracle_base="/usr/oracle/app"#ORACLE_BASE set from environment orcl.__pga_aggregate_target=1325400064 orcl.__sga_target=1979711488 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=436207616 orcl.__streams_pool_size=0 *.audit_file_dest="/usr/oracle/app/admin/orcl/adump" *.audit_trail="db" *.compatible="11.2.0.0.0" *.control_files="/usr/oracle/app/oradata/orcl/control01.ctl","/usr/oracle/app/flash_recovery_area/orcl/control02.ctl" *.db_block_size=8192 *.db_domain="" *.db_name="orcl" *.db_recovery_file_dest="/usr/oracle/app/flash_recovery_area" *.db_recovery_file_dest_size=4070572032 *.diagnostic_dest="/usr/oracle/app" *.dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)" *.memory_target=3299868672 *.nls_language="SIMPLIFIED CHINESE" *.nls_territory="CHINA" *.open_cursors=300 *.processes=150 *.remote_login_passwordfile="EXCLUSIVE" *.undo_tablespace="UNDOTBS1" *.DB_UNIQUE_NAME=db_standby *.LOG_ARCHIVE_CONFIG="DG_CONFIG=(db_primary,db_standby)" *.LOG_ARCHIVE_DEST_2="SERVICE=tns_primary ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_primary" #ARCH为已归档日志 *.LOG_ARCHIVE_DEST_STATE_2=ENABLE #备用的连接到主的可以开启,因为此时主同步日志到备未启用 *.FAL_SERVER=tns_primary *.FAL_CLIENT=tns_standby *.STANDBY_FILE_MANAGEMENT=AUTO

StandBy> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo Buffers 16568320 bytes

# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /usr/oracle/app

# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools. #红色字体为静态监听,后面duplicate需要用到静态监听注册 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = StandBy) (ORACLE_HOME =/usr/oracle/app/product/11.2.0/dbhome_1) (SID_NAME = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /usr/oracle/app

[oracle@oracledb admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:15 Copyright (c) 1991, 2009, Oracle. All rights reserved. Starting /usr/oracle/app/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.1.0 - Production System parameter file is /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 23-DEC-2015 15:48:16 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521))) Services Summary... Service "StandBy" has 1 instance(s).  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

tns_primary =
  (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db_primary) ) ) tns_standby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =StandBy ) ) )

[oracle@oracledb admin]$ tnsping tns_primary

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:44 Copyright (c) 1997, 2009, Oracle. All rights reserved. Used parameter files: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))) OK (0 msec) [oracle@oracledb admin]$ tnsping tns_standby TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:49 Copyright (c) 1997, 2009, Oracle. All rights reserved. Used parameter files: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))) OK (0 msec)

[oracle@oracledb admin]$ rman target sys/primary_password@tns_primary auxiliary sys/standby_password@tns_standby

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 23 16:11:51 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1426832466) connected to auxiliary database: ORCL (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Starting Duplicate Db at 23-DEC-15 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=20 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 12/23/2015 16:13:38 RMAN-05541: no archived logs found in target database

Primary>show parameter LOG_ARCHIVE_DEST_STATE_2

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_state_2 string DEFER #当前主的还未启用 log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable
 Primary>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE; System altered.

Primary>select database_role from v$database;

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

StandBy>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY StandBy>

Primary> INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ("12", "OPERATIONS", "OPERATIONS");
row created.

Primary>commit; Commit complete. Primary>alter system switch logfile; System altered.

StandBy>alter database recover managed standby database disconnect from session; #standby端应用redo

Database altered. StandBy>alter database recover managed standby database cancel; #取消应用redo Database altered. StandBy>alter database open; #打开数据库 Database altered. StandBy>select open_mode from v$database; #standby是只读状态 OPEN_MODE ---------------------------------------- READ ONLY StandBy>

StandBy>select * from scott.dept;

DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
ACCOUNTING NEW YORK RESEARCH DALLAS SALES CHICAGO OPERATIONS BOSTON OPERATIONS OPERATIONS

Primary>col group# for 9
Primary>col status for a10
Primary>col type for a10 Primary>col member for a50; Primary>col is_rec for a10 Primary>select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_REC ------ ---------- ---------- -------------------------------------------------- ------ ONLINE /usr/oracle/app/oradata/orcl/redo03.log NO ONLINE /usr/oracle/app/oradata/orcl/redo02.log NO ONLINE /usr/oracle/app/oradata/orcl/redo01.log NO Primary>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ------ ---------- ---------- ---------- ---------- ---------- ------ ---------- ------------- ------------ ------------ ------------ 1 10 52428800 512 1 NO CURRENT 1029234 23-DEC-15 2.8147E+14 1 8 52428800 512 1 YES INACTIVE 1028340 23-DEC-15 1028421 23-DEC-15 1 9 52428800 512 1 YES INACTIVE 1028421 23-DEC-15 1029234 23-DEC-15

Primary>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 4("/usr/oracle/app/oradata/orcl/stbyredolog4a.log") SIZE  50 M;

Database altered. Primary>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5("/usr/oracle/app/oradata/orcl/stbyredolog5a.log") SIZE 50 M; Database altered. Primary>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6("/usr/oracle/app/oradata/orcl/stbyredolog6a.log") SIZE 50 M; Database altered. Primary>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7("/usr/oracle/app/oradata/orcl/stbyredolog7a.log") SIZE 50 M; Database altered.

Primary>select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_REC ------ ---------- ---------- -------------------------------------------------- ------ ONLINE /usr/oracle/app/oradata/orcl/redo03.log NO ONLINE /usr/oracle/app/oradata/orcl/redo02.log NO ONLINE /usr/oracle/app/oradata/orcl/redo01.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog4a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog5a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog6a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog7a.log NO rows selected.

StandBy>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 4("/usr/oracle/app/oradata/orcl/stbyredolog4a.log") SIZE  50 M;

Database altered. StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5("/usr/oracle/app/oradata/orcl/stbyredolog5a.log") SIZE 50 M; Database altered. StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6("/usr/oracle/app/oradata/orcl/stbyredolog6a.log") SIZE 50 M; Database altered. StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7("/usr/oracle/app/oradata/orcl/stbyredolog7a.log") SIZE 50 M; Database altered.

StandBy>select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_REC ------ ---------- ---------- ---------------------------------------------------------------------------------------------------- ------ ONLINE /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_3_c7np1p97_.log YES ONLINE /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_2_c7np1osm_.log YES ONLINE /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_1_c7np1od8_.log YES STANDBY /usr/oracle/app/oradata/orcl/stbyredolog4a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog5a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog6a.log NO STANDBY /usr/oracle/app/oradata/orcl/stbyredolog7a.log NO

Primary>show parameter log_archive_dest_2

NAME                     TYPE    VALUE
------------------------------------ ---------- ------------------------------
log_archive_dest_2             string    SERVICE=tns_standby ARCH VALID _FOR=(ONLINE_LOGFILES,PRIMARY_ ROLE) DB_UNIQUE_NAME=db_standb y log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string
 Primary>alter system set log_archive_dest_2="SERVICE=tns_standby LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby"; #以前此处为ARCH, System altered. Primary>show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ---------- ------------------------------ log_archive_dest_2 string SERVICE=tns_standby LGWR VALID _FOR=(ONLINE_LOGFILES,PRIMARY_ ROLE) DB_UNIQUE_NAME=db_standb y log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string Primary>

StandBy>alter system set log_archive_dest_2="SERVICE=tns_primary LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_primary";

StandBy>alter database recover managed standby database using current logfile  disconnect from session;   #因为更改成了LGWR来传递,所以需要使用实时

Primary>select * from scott.dept;

    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
ACCOUNTING NEW YORK RESEARCH DALLAS SALES CHICAGO OPERATIONS BOSTON OPERATIONS OPERATIONS Primary>INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ("13", "OPERATIONS", "OPERATIONS"); row created. Primary>commit; Commit complete.

StandBy>select * from scott.dept;
    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
ACCOUNTING NEW YORK RESEARCH DALLAS SALES 　　 CHICAGO OPERATIONS BOSTON OPERATIONS OPERATIONS OPERATIONS OPERATIONS rows selected.

StandBy>alter database recover managed standby database cancel; #取消redo应用

Database altered.

Primary> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
TO STANDBY

StandBy> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
NOT ALLOWED

Primary>alter database commit to switchover to   physical standby;  #将当前primary角色切换到物理standby

Database altered. Primary>shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. Primary>startup ORACLE instance started. Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 1795164312 bytes Database Buffers 1476395008 bytes Redo Buffers 16568320 bytes Database mounted. Database opened. Primary>select open_mode from v$database; OPEN_MODE ---------------------------------------- READ ONLY Primary>select database_role from v$database; DATABASE_ROLE -------------------------------- PHYSICAL STANDBY Primary>

StandBy>select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
TO PRIMARY StandBy> alter database commit to switchover to primary;  #standby切换到primary Database altered. StandBy>alter database open; Database altered. StandBy>select open_mode from v$database; OPEN_MODE ---------------------------------------- READ WRITE StandBy>select database_role from v$database; DATABASE_ROLE -------------------------------- PRIMARY StandBy>

select process,client_process,sequence#,status from v$managed_standby;

select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from v$archive_dest_status where status ="VALID";

select name,creator,sequence#,applied,completion_time from v$archived_log;

select first_time,first_change#,next_change#,sequence# from v$log_history;

select thread#,sequence#,applied from v$archived_log;

select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;

select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;

StandBy>select recovery_mode from v$archive_dest_status where dest_id=2;

RECOVERY_MODE
----------------------------------------------
IDLE