Check Whether Physical Standby Is In Sync With The Primary Or NOT

Check Whether Physical Standby Is In Sync With The Primary Or NOT

Method.1

set lines 555

select instance_name,name,open_mode,to_char(startup_time,'DD-MM-YY:HH24:MI:ss') startup_time from v$database,v$instance;

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

+ From Primary:

SQL > select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

SQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

+ From Physical Standby:

SQL > select thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

SQL > select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and applied='YES' group by thread# order by 1;

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

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#;

select max(sequence#) from v$log_history;

select max(sequence#) from v$archived_log where applied='YES';

--Without realtime:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

--With realtime apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Method.2

From Primary Side:

select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

select thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

select max(sequence#) from v$log_history;

v$archived_log

gv$archive_dest

v$database

gv$instance 

 

From Physical Standby Side:

select thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and applied='YES' group by thread# order by 1;

select max(sequence#) from v$log_history;

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

 

v$archived_log

gv$archive_dest

v$database

v$managed_standby 

 

Primary Side:

SQL> select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

 THREAD# Last Primary Seq Generated

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

         1                       2915

 

SQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

   THREAD#    DEST_ID STATUS

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

ERROR                                                             FAIL_SEQUENCE

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

         1          1 VALID

         1          2 VALID 0

SQL>select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

————–

2915

                                                                              

Standby Side:

SQL> select thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

THREAD# Last Standby Seq Received

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

         1                      2915

 

SQL> select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and applied='YES' group by thread# order by 1;

 THREAD# Last Standby Seq Applied

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

         1                     2915

SQL>select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

————–

2915

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

PROCESS   STATUS          THREAD#  SEQUENCE#

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

DGRD      ALLOCATED             0          0

ARCH      CLOSING               1       2897

DGRD      ALLOCATED             0          0

ARCH      CLOSING               1       2914

ARCH      CLOSING               1       2893

ARCH      CLOSING               1       2915

MRP0      APPLYING_LOG          1       2916

RFS       IDLE                  1          0

RFS       IDLE                  1       2916

9 rows selected.

Difference Between ASMM & AMM

Difference Between ASMM & AMM

Automatic Shared Memory Management

Automatic Memory Management

About SQL Hints

About SQL Hints

* A hint is an instruction to the optimizer to follow an ‘application developer desired’ execution plan 

* Make decisions for the optimizer because you have more in-depth knowledge about data distribution patterns

* In a test or development environments, hints are useful for testing the performance of a specific access path- test use of a specific index when there are several indexes defined on the same table 

* Changes in the database or host environment can make hints obsolete and  even have negative consequences

* Recommended to use tools like SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to address performance problems not solved by the optimizer before considering hints

How do we gather optimizer statistics?

How do we gather optimizer statistics?

* By default, Oracle Database uses automatic optimizer statistics collection

* Database automatically runs DBMS_STATS to collect optimizer statistics for all schema objects for which statistics are missing or stale

* You can also update and manage optimizer statistics by manually executing DBMS_STATS

* Also, when optimizer statistics are missing, stale, or insufficient, the database automatically gathers dynamic statistics during a parse (depends on level of dynamic sampling)

Automatic Statistics Gathering

Automatic Statistics Gathering 

What is An Optimizer ?

What is An Optimizer ?

* Generates the optimal execution plan 

* Determines the most efficient method for a SQL statement to access requested data

* The optimizer choose the plan with the lowest cost among all considered candidate plans

* The optimizer uses available statistics to calculate cost

* Cost computation accounts for factors of query execution like I/O , CPU, Network, Memory