DGMGRL show configuration reporting ORA-16810: multiple errors or warnings detected for the database

I was getting the error on primary an standby databases under show configuration .

DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC      - Primary database
      Error: ORA-16810: multiple errors or warnings detected for the database
    RAC_STBY - Physical standby database
      Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL> 

To get more details I looked at individual database

DGMGRL>
DGMGRL> show database 'RAC'
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    RAC1
      Error: ORA-16737: the redo transport service for standby database "RAC_STBY" has an error
    RAC2
      Error: ORA-16737: the redo transport service for standby database "RAC_STBY" has an error
Database Status:
ERROR
DGMGRL>

This prompted me to see the alert logs of primary database where I was getting error that primary is not able to connect to standby

***********************************************************************
Fatal NI connect error 12528, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racdr1-vip)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC_STBY.localdomain)(CID=(PROGRAM=oracle)(HOST=rac1.localdomain)(USER=oracle))))
  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0 - Production
  Time: 27-MAR-2014 13:23:09
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564
 
TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
***********************************************************************

My immediate response was to see if the database on stanby is running or which was not

[oracle@racdr1 ~]$ srvctl status database -d RAC_STBY
Instance RAC_STBY1 is not running on node racdr1
[oracle@racdr1 ~]$ 

After starting the stand by database.

[oracle@racdr1 ~]$ srvctl start database -d RAC_STBY
[oracle@racdr1 ~]$ 

I was too quick on primary issuing the statement and found that oracle now knows that we are starting standby database.

DGMGRL> show configuration

Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC      - Primary database
    RAC_STBY - Physical standby database
      Error: ORA-16770: Redo Apply not started since physical standby database is opening
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL>

Once the database was started on standby all went good.

DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC      - Primary database
    RAC_STBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL> show database 'RAC'
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    RAC1
    RAC2
Database Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL> show database 'RAC_STBY'
Database - RAC_STBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    RAC_STBY1
Database Status:
SUCCESS
DGMGRL> 

oracle how to gather stats, backup them and use the backed up stats

Oracle stats are used by optimiser to determine the query plan. Query plan is how the query will be executed by oracle.

Plan determine if the query will use full table scan of the table / use indexes / use hash join / nested loops etc to get the final outcome from the query. All of this depends on what the stats are and stats will hold the number of the rows in table, cardinality, size of data and many more.

It is not always good idea to gather stats on tables all the time something they may change the query plan which might not be optimal for your scenario. So if you want to gather stats then always a good idea to export the stats of that table and then gather stats.

In this post I will discuss on how to export and import stats.

Steps:
1. Create a table which will store the exported stats
2. Grant permission to use this table
3. Export the stats (schema , table, index etc. )
4. Gather new stats on object
5. If required import stats back from exported table

Type of exports you can do in oracle 11g
EXPORT_COLUMN_STATS
EXPORT_DATABASE_STATS
EXPORT_DICTIONARY_STATS
EXPORT_FIXED_OBJECTS_STATS
EXPORT_INDEX_STATS
EXPORT_SCHEMA_STATS
EXPORT_SYSTEM_STATS
EXPORT_TABLE_STATS

Check Current statistics:

select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where TABLE_NAME='EMP';

Create stats table:

EXEC DBMS_STATS.CREATE_STAT_TABLE (ownname=>'HDHILLON', stattab=>'STATS_SCHEMA_EMP', tblspace=>'MICHAEL'); 

Grant Permissions:

GRANT SELECT,INSERT,UPDATE,DELETE on CSU.STATS_SCHEMA_EMP TO PUBLIC;

Export statistics:

exec dbms_stats.export_schema_stats (ownname =>'HDHILLON',stattab=>'STATS_SCHEMA_EMP'); 

Gather stats for HDHILLON.EMP table:

execute dbms_stats.gather_table_stats(ownname=>'HDHILLON', tabname=>'EMP', estimate_percent => 10, method_opt => 'for all columns size skewonly',  cascade=> true);

Check new statistics:

select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where TABLE_NAME='EMP';

Importing previously backed up stats:

exec dbms_stats.import_table_stats ('HDHILLON', 'EMP', stattab=>'STATS_SCHEMA_EMP'); 

Import procedures which can be used are:

IMPORT_COLUMN_STATS 

IMPORT_DATABASE_STATS 

IMPORT_DICTIONARY_STATS 

IMPORT_FIXED_OBJECTS_STATS 

IMPORT_INDEX_STATS 

IMPORT_SCHEMA_STATS 

IMPORT_SYSTEM_STATS 

IMPORT_TABLE_STATS 

Example of exporting table stats is:

exec DBMS_STATS.EXPORT_TABLE_STATS (ownname =>'HDHILLON', tabname => 'EMP', stattab=>'STATS_SCHEMA_EMP' ); 

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

Export fixed object stats

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

exec dbms_stats.create_stat_table('HDHILLON','HARVEY_STATS_TABLE','USERS');


select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED        FIXED_OBJECTS
-------------------- -------------
NO STATS                       950

SQL>

SQL> select * from HARVEY_STATS_TABLE;

no rows selected

SQL>

 SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED        FIXED_OBJECTS
-------------------- -------------
NO STATS                       117
2015-Feb-20                    833

SQL>

exec dbms_stats.export_fixed_objects_stats(stattab=>'HARVEY_STATS_TABLE',statown=>'HDHILLON');

SQL> select count(*) from HARVEY_STATS_TABLE;

  COUNT(*)

----------

     23578

SQL> 

------------------
Import fixed object stats
------------------
select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED        FIXED_OBJECTS
-------------------- -------------
NO STATS                       117
2015-Feb-20                    833

SQL>

exec dbms_stats.delete_fixed_objects_stats();

select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED        FIXED_OBJECTS

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

NO STATS                       950

SQL> 

exec dbms_stats.import_fixed_objects_stats(stattab=>'HARVEY_STATS_TABLE',statown=>'HDHILLON');

select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED        FIXED_OBJECTS
-------------------- -------------
NO STATS                       117
2015-Feb-20                    833

SQL>

Ever want to drop the stats table

SQL> exec  dbms_stats.drop_stat_table('HDHILLON','HARVEY_STATS_TABLE');

Syntax:

DBMS_STATS.DROP_STAT_TABLE (
   ownname VARCHAR2, 
   stattab VARCHAR2);