Get the SQL_ID of the statement

1. Run the sql statement.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRDWCS7   READ WRITE


2. Run below 
select * from table(dbms_xplan.display_cursor(null));


SQL> select * from table(dbms_xplan.display_cursor(null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9czut73sxc2y4, child number 0
-------------------------------------
select name,open_mode from v$database

Plan hash value: 3039639316

----------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN|          |     1 |    14 |     0   (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    14 |     0   (0)|
|   3 |   BUFFER SORT        |          |     1 |       |     0   (0)|
|   4 |    FIXED TABLE FULL  | X$KCCDI2 |     1 |       |     0   (0)|
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))


21 rows selected.


3. you will get the output.

SQL_ID  9czut73sxc2y4

Script to Delete Applied Archivelog from Standby ( RAC )

#!/bin/ksh
export ORACLE_SID=ORCL
export ORACLE_HOME=/app/oracle/product/12102/db
export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export TODAY=`date '+%Y%m%d'`
export TIME=`date '+%H%M%S'`
rm /app/oracle/dba/script/notearc.txt
sqlplus -s "/ as sysdba" << EOF
set pagesize 0
set pages 0 echo off feedback off
spool /app/oracle/dba/script/notearc.txt
select thread#,max(sequence#)-10 from gv\$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread#;
spool off;
exit;
EOF

seq1=`cat /app/oracle/dba/script/notearc.txt | awk 'NR==1{print $2}'`
seq2=`cat /app/oracle/dba/script/notearc.txt | awk 'NR==2{print $2}'`
seq3=`cat /app/oracle/dba/script/notearc.txt | awk 'NR==3{print $2}'`
echo $seq1 $seq2 $seq3
$ORACLE_HOME/bin/rman msglog /app/oracle/dba/logs/STANDBYDBY_ARC_DEL.${TODAY}_${TIME}.log << EOF
connect target /
run{
delete noprompt archivelog until sequence $seq1 thread 1;
delete noprompt archivelog until sequence $seq2 thread 2;
delete noprompt archivelog until sequence $seq3 thread 3;
}
EOF

-------------------------------------crontab------------------------------

05 02 * * * /u01/app/oracle/cleanarc.ksh > /u01/app/oracle/clean_arc.log 2>&1

12c Create Dataguard Broker Configuration - DGMGRL for RAC

==> Pre-Check

Make sure below is set to false on primary and Standby.

#primary
SQL> sho parameter dg_broker_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE

#standby
SQL> sho parameter dg_broker_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE


1. Create config files on ASM DiskGroup.

==>On primary
alter system set dg_broker_config_file1 = '+RTLDATA01/ORCL/DATAFILE/dr1ORCL.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+RTLDATA01/ORCL/DATAFILE/dr2ORCL.dat' scope=both sid='*';

P.S. Config file name has to be DB_UNIQUE_NAME

SQL> alter system set dg_broker_config_file1 = '+RTLDATA01/ORCL/DATAFILE/dr1ORCL.dat' scope=both sid='*';

System altered.

SQL> alter system set dg_broker_config_file2 = '+RTLDATA01/ORCL/DATAFILE/dr2ORCL.dat' scope=both sid='*';

System altered.

SQL> sho parameter config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +RTLDATA01/ORCL/DATAFILE/dr1
                                                 ORCL.dat
dg_broker_config_file2               string      +RTLDATA01/ORCL/DATAFILE/dr2
                                                 ORCL.dat

==>On Standby
alter system set dg_broker_config_file1 = '+MDRDATA01/DRORCL/DATAFILE/dr1DRORCL.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+MDRDATA01/DRORCL/DATAFILE/dr2DRORCL.dat' scope=both sid='*';

P.S. Config file name has to be DB_UNIQUE_NAME
SQL> alter system set dg_broker_config_file1 = '+MDRDATA01/DRORCL/DATAFILE/dr1DRORCL.dat' scope=both sid='*';

System altered.

SQL> alter system set dg_broker_config_file2 = '+MDRDATA01/DRORCL/DATAFILE/dr2DRORCL.dat' scope=both sid='*';

System altered.

SQL> sho parameter config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +MDRDATA01/DRORCL/DATAFILE/d
                                                 r1DRORCL.dat
dg_broker_config_file2               string      +MDRDATA01/DRORCL/DATAFILE/d
                                                 r2DRORCL.dat


2. Start the Dataguard broker DMON process.

On Primary and Standby,

alter system set dg_broker_start=true sid='*'; 

##primary
SQL> alter system set dg_broker_start=true sid='*';

System altered.


#standby##
SQL> alter system set dg_broker_start=true sid='*';

System altered.


3.Take backup of log_archive_dest_2 and run below on primary and standby


Show parameter log_archive_dest_2


##primary
SQL> alter system set log_archive_dest_2='' scope=both sid='*';

System altered.


#standby##
SQL> alter system set log_archive_dest_2='' scope=both sid='*';

System altered.


4. Connect to DGMGRL

On Primary,

node1:/app/oracle/product/12.1.0.2/db/dbs>dgmgrl
DGMGRL for Solaris: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected as SYSDG.
DGMGRL>


5. Create configuration,


DGMGRL> CREATE CONFIGURATION '<configuration name>' AS PRIMARY DATABASE IS '<primary db_unique_name>' CONNECT IDENTIFIER IS <primary connect string>;


CREATE CONFIGURATION ORCL_DG_CONFIG AS PRIMARY DATABASE IS ORCL CONNECT IDENTIFIER IS ORCL;


DGMGRL> CREATE CONFIGURATION ORCL_DG_CONFIG AS PRIMARY DATABASE IS ORCL CONNECT IDENTIFIER IS ORCL;
Configuration "ORCL_dg_config" created with primary database "ORCL"
DGMGRL>


Verify configuration,

DGMGRL> show configuration;

Configuration - ORCL_dg_config

  Protection Mode: MaxPerformance
  Members:
  ORCL - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED


6. Add Standby,

DGMGRL>  add database <standby db_unique_name> as connect identifier is <standby connect string>;

ADD DATABASE DRORCL AS CONNECT IDENTIFIER IS DRORCL;



DGMGRL> ADD DATABASE DRORCL AS CONNECT IDENTIFIER IS DRORCL;
Database "drORCL" added
DGMGRL>


DGMGRL> show configuration;

Configuration - ORCL_dg_config

  Protection Mode: MaxPerformance
  Members:
  ORCL   - Primary database
    drORCL - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL>


7. Enable Configuration,

DGMGRL> enable configuration;
Enabled.
DGMGRL>


6. Verify configuration,

DGMGRL>


DGMGRL> show configuration;

Configuration - ORCL_dg_config

  Protection Mode: MaxPerformance
  Members:
  ORCL   - Primary database
    drORCL - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 10 seconds ago)

DGMGRL>



DGMGRL> show database ORCL;

Database - ORCL

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    ORCL1
    ORCL2
    ORCL3

Database Status:
SUCCESS

DGMGRL> show database drORCL;

Database - drORCL

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 7.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    ORCL1 (apply instance)
    ORCL2
    ORCL3

Database Status:
SUCCESS


#Reference Doc
12c Create Dataguard Broker Configuration - DGMGRL (Doc ID 1583588.1)
Data Guard Broker Return ORA-16664 Unable To Receive The Result From A Database (Doc ID 2138555.1)