nabhesh-joshi-oracledba

Friday, 15 September 2017

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

Sunday, 3 September 2017

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)

Tuesday, 29 August 2017

DBA Jobs details

set markup html on spool on
spool scheduler_check_norun.html
set echo on
alter session set nls_timestamp_tz_format='DD-MON-RR HH.MI.SS AM TZR';
alter session set nls_date_format='DD-MON-RR HH.MI.SS AM';
show parameter job_queue_processes
select * from dba_scheduler_jobs;
select * from dba_scheduler_windows;
select * from dba_scheduler_window_details where log_date >= sysdate-10 order by log_date desc;
select * from dba_scheduler_window_log order by log_date desc;
select * from dba_scheduler_window_groups;
select * from dba_scheduler_job_run_details where log_date >= sysdate-10 order by log_date desc;
select * from dba_scheduler_job_log where log_date >= sysdate-10 order by log_date desc;
spool off
set markup html off

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
set pagesize 9999
spool /tmp/dba_autotask_client.html
set markup html on
select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
select * from DBA_AUTOTASK_CLIENT_JOB;
select * from DBA_AUTOTASK_JOB_HISTORY order by JOB_START_TIME;
select * from DBA_AUTOTASK_OPERATION;
select * from DBA_AUTOTASK_SCHEDULE order by START_TIME;
select * from DBA_AUTOTASK_TASK;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
select * from DBA_AUTOTASK_WINDOW_HISTORY order by WINDOW_START_TIME;
select * from dba_scheduler_windows;
select * from dba_scheduler_window_groups;
select * from dba_scheduler_job_run_details order by ACTUAL_START_DATE;
select * from DBA_SCHEDULER_JOB_LOG;
SELECT program_name, program_action, enabled FROM dba_scheduler_programs;
spool off

Truncate Audit Trail Table AUD$

###Take the export###

exp aud_backup/audit file=/local/oracle/UATWCS7/aud_backup.dmp log=aud_backup.log tables=SYS.AUD$ buffer=5000000

log==>
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table AUD$ 929290860 rows exported
EXP-00091: Exporting questionable statistics.

##
NAME GBYTES USED FREE PCT_USED LARGEST MAX_SIZE PCT_MAX_USED EXTENT_MAN SEGMEN
------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ------
SYSTEM 161.82 159.12 2.7 98.33 1536 157 101.35 LOCAL MANUAL

col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM' ORDER BY BLOCKS desc) where rownum < 11;

OWNER SEGMENT_NAME SIZE_M
------ -------------------------------------------------- ----------
SYS AUD$~ 160929 ==========>>>>>THIS
SYS FGA_LOG$~ 984
SYS IDL_UB1$~ 256
SYS SYS_LOB0000000394C00013$$~ 96
SYS SOURCE$~ 80
SYS SYS_LOB0000403209C00045$$~ 71
SYS C_OBJ#_INTCOL#~ 37
SYS IDL_UB2$~ 32
SYS C_TOID_VERSION#~ 24
SYS SYS_LOB0000001067C00003$$~ 20

###script####

#!/bin/bash
export ORACLE_SID=ABC
export ORACLE_HOME=/app/oracle/product/11.2.0/db
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/lib:$PATH
sqlplus "/as sysdba"<<-EOF
set echo on;
set serveroutput on;
set lines 200 pages 200
set time on;
set timing on;
set feedback on
spool TRUNCATE_SYS_AUD.log
Show user;
show parameter db_name
show parameter instance_number
SELECT NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME FROM V\$DATABASE;
alter session enable parallel ddl;
alter session force parallel ddl parallel 20;
alter session set db_file_multiblock_read_count = 256;
alter session set "_px_max_granules_per_slave"=3000;
alter session set "_px_index_sampling"=3000;
alter session set "_px_dynamic_sample_size"=3000;
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
alter session set parallel_force_local=TRUE;
truncate table aud$;
spool off
EOF
exit

##LOG===>

18:48:51 SQL> alter session enable parallel ddl;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session force parallel ddl parallel 20;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set db_file_multiblock_read_count = 256;

Session altered.

Elapsed: 00:00:00.01
18:48:51 SQL> alter session set "_px_max_granules_per_slave"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set "_px_index_sampling"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set "_px_dynamic_sample_size"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;

Session altered.

Elapsed: 00:00:00.01
18:48:51 SQL> alter session set parallel_force_local=TRUE;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> truncate table aud$;

Table truncated.

Elapsed: 00:00:02.55
18:48:54 SQL> spool off

###
col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM' ORDER BY BLOCKS desc) where rownum < 11;

OWNER SEGMENT_NAME SIZE_M
------ -------------------------------------------------- ----------
SYS FGA_LOG$~ 984
SYS IDL_UB1$~ 256
SYS SYS_LOB0000000394C00013$$~ 96
SYS SOURCE$~ 80
SYS SYS_LOB0000403209C00045$$~ 71
SYS C_OBJ#_INTCOL#~ 37
SYS IDL_UB2$~ 32
SYS C_TOID_VERSION#~ 24
SYS C_OBJ#~ 20
SYS SYS_LOB0000001067C00003$$~ 20

10 rows selected.

NAME GBYTES USED FREE PCT_USED LARGEST MAX_SIZE PCT_MAX_USED EXTENT_MAN SEGMEN

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

SYSTEM 161.82 1.96 159.86 1.21 3968 157 1.25 LOCAL MANUAL

How to Truncate, Delete, or Purge Rows from the Audit Trail Table AUD$ (Doc ID 73408.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=240046292784578&id=73408.1&_afrWindowMode=0&_adf.ctrl-state=12uh2plej6_4

Thursday, 3 August 2017

Insufficient SRLs reported by DGMGRL VALIDATE DATABASE-12c

DGMGRL> validate database 'STG_PROD';

Database Role: Primary database

Ready for Switchover: Yes

DGMGRL> validate database 'stg_prod_dg';

Database Role: Physical standby database
Primary Database: STG_PROD

Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)

Flashback Database Status:
STG_PROD: On
stg_prod_dg: Off

Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(STG_PROD) (stg_prod_dg)
1 11 5 Insufficient SRLs ======>>>>>>THIS

Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(stg_prod_dg) (STG_PROD)
1 11 5 Insufficient SRLs ======>>>>>>THIS

This is because if you see the thread#, it is showing as 0 which is incorrect. it has to be 1 ( stand alone DB)

SQL> set pages 200
SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

THREAD# GROUP# SEQUENCE# STATUS BYTES
---------- ---------- ---------- ---------- ----------
1 12 0 UNASSIGNED 419430400
1 13 54 ACTIVE 419430400
1 14 0 UNASSIGNED 419430400
1 15 0 UNASSIGNED 419430400
1 16 0 UNASSIGNED 419430400
0 17 0 UNASSIGNED 419430400
0 18 0 UNASSIGNED 419430400
0 19 0 UNASSIGNED 419430400
0 20 0 UNASSIGNED 419430400
0 21 0 UNASSIGNED 419430400
0 22 0 UNASSIGNED 419430400
0 23 0 UNASSIGNED 419430400

###

SQL> set pages 2000

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

THREAD# GROUP# SEQUENCE# STATUS BYTES

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

1 12 0 UNASSIGNED 419430400

1 13 0 UNASSIGNED 419430400

1 14 0 UNASSIGNED 419430400

1 15 0 UNASSIGNED 419430400

1 16 0 UNASSIGNED 419430400

0 17 0 UNASSIGNED 419430400

0 18 0 UNASSIGNED 419430400

0 19 0 UNASSIGNED 419430400

0 20 0 UNASSIGNED 419430400

0 21 0 UNASSIGNED 419430400

0 22 0 UNASSIGNED 419430400

0 23 0 UNASSIGNED 419430400

##Solution.

SQL> alter database drop standby logfile group 17;

alter database drop standby logfile group 17

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

##Stop the MRP on STANDBY###

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database drop standby logfile group 17;

Database altered.

SQL> alter database drop standby logfile group 18;

Database altered.

SQL> alter database drop standby logfile group 19;

Database altered.

SQL> alter database drop standby logfile group 20;

Database altered.

SQL> alter database drop standby logfile group 21;

Database altered.

SQL> alter database drop standby logfile group 22;

Database altered.

SQL> alter database drop standby logfile group 23;

Database altered.

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

THREAD# GROUP# SEQUENCE# STATUS BYTES

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

1 12 0 UNASSIGNED 419430400

1 13 54 ACTIVE 419430400

1 14 0 UNASSIGNED 419430400

1 15 0 UNASSIGNED 419430400

1 16 0 UNASSIGNED 419430400

SQL> alter database add standby logfile thread 1 group 17 size 419430400;

Database altered.

SQL> alter database add standby logfile thread 1 group 18 size 419430400;

alter database add standby logfile thread 1 group 19 size 419430400;

alter database add standby logfile thread 1 group 20 size 419430400;

alter database add standby logfile thread 1 group 21 size 419430400;

alter database add standby logfile thread 1 group 22 size 419430400;

alter database add standby logfile thread 1 group 23 size 419430400;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

THREAD# GROUP# SEQUENCE# STATUS BYTES

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

1 12 0 UNASSIGNED 419430400

1 13 54 ACTIVE 419430400

1 14 0 UNASSIGNED 419430400

1 15 0 UNASSIGNED 419430400

1 16 0 UNASSIGNED 419430400

1 17 0 UNASSIGNED 419430400

1 18 0 UNASSIGNED 419430400

1 19 0 UNASSIGNED 419430400

1 20 0 UNASSIGNED 419430400

1 21 0 UNASSIGNED 419430400

1 22 0 UNASSIGNED 419430400

1 23 0 UNASSIGNED 419430400

12 rows selected.

Do the same on Primary as well.

SQL> alter database drop standby logfile group 17;

alter database drop standby logfile group 18;

alter database drop standby logfile group 19;

alter database drop standby logfile group 20;

alter database drop standby logfile group 21;

alter database drop standby logfile group 22;

alter database drop standby logfile group 23;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

THREAD# GROUP# SEQUENCE# STATUS BYTES

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

1 12 0 UNASSIGNED 419430400

1 13 0 UNASSIGNED 419430400

1 14 0 UNASSIGNED 419430400

1 15 0 UNASSIGNED 419430400

1 16 0 UNASSIGNED 419430400

SQL> alter database add standby logfile thread 1 group 17 size 419430400;

alter database add standby logfile thread 1 group 18 size 419430400;

alter database add standby logfile thread 1 group 19 size 419430400;

alter database add standby logfile thread 1 group 20 size 419430400;

alter database add standby logfile thread 1 group 21 size 419430400;

alter database add standby logfile thread 1 group 22 size 419430400;

alter database add standby logfile thread 1 group 23 size 419430400;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

THREAD# GROUP# SEQUENCE# STATUS BYTES

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

1 12 0 UNASSIGNED 419430400

1 13 0 UNASSIGNED 419430400

1 14 0 UNASSIGNED 419430400

1 15 0 UNASSIGNED 419430400

1 16 0 UNASSIGNED 419430400

1 17 0 UNASSIGNED 419430400

1 18 0 UNASSIGNED 419430400

1 19 0 UNASSIGNED 419430400

1 20 0 UNASSIGNED 419430400

1 21 0 UNASSIGNED 419430400

1 22 0 UNASSIGNED 419430400

1 23 0 UNASSIGNED 419430400

12 rows selected.

## Start the MRP on standby

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process , status , sequence#,thread# from v$managed_standby ;

PROCESS STATUS SEQUENCE# THREAD#

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

ARCH CLOSING 52 1

ARCH CONNECTED 0 0

ARCH CLOSING 53 1

ARCH CLOSING 51 1

RFS IDLE 54 1

RFS IDLE 0 0

MRP0 APPLYING_LOG 54 1

9 rows selected.

##Now check the switchover status

DGMGRL> validate database 'STG_PROD';

Database Role: Primary database

Ready for Switchover: Yes <-----------Ready for Switchover

DGMGRL> validate database 'stg_prod_dg';

Database Role: Physical standby database

Primary Database: STG_PROD

Ready for Switchover: Yes <--------This confirms Switchover readiness

Ready for Failover: Yes (Primary Running)

Flashback Database Status:

STG_PROD: On

stg_prod_dg: Off

Sunday, 2 July 2017

Get explain plans of a sql query from AWR using DBMS_XPLAN.DISPLAY_AWR

set lines 2000 pages 2000
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('7q4fs7yuu5fyk'));