Category: Oracle

No Stats, Stale Stats and Locked Stats in Oracle Objects

N0 Stats

SQL> SELECT OWNER, TABLE_NAME from dba_tables where last_analyzed is null;

SQL> SELECT OWNER, TABLE_NAME from dba_indexes  where last_analyzed is null;

SQL>  select count(*) from dba_ind_statistics where LAST_ANALYZED is null;

  COUNT(*)
----------
       795

SQL>  select count(*) from dba_tab_statistics where LAST_ANALYZED is null;

  COUNT(*)
----------
       406

Stale Stats

SQL> SELECT OWNER, TABLE_NAME, STALE_STATS FROM DBA_TAB_STATISTICS WHERE STALE_STATS='YES' ;

SQL>  SELECT OWNER, TABLE_NAME, STALE_STATS FROM DBA_IND_STATISTICS WHERE STALE_STATS='YES';

Locked Stats

 SQL> SELECT OWNER, TABLE_NAME, STATTYPE_LOCKED FROM DBA_TAB_STATISTICS
WHERE STATTYPE_LOCKED IS NOT NULL ;

SQL> SELECT OWNER, TABLE_NAME, STATTYPE_LOCKED FROM DBA_IND_STATISTICS WHERE STATTYPE_LOCKED IS NOT NULL;

Collect stats

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

Oracle ASM Thin Provisioned Attribute and Reclaim Storage Space

ASMFD Installation & Configuration

Verify the ASM Filer Driver (AFD) Kernel module has been loaded.

$ lsmod | grep afd
oracleafd             226702  1

To confirm ASM Filter Driver has already been installed, loaded, is supported and versions, use afddriverstate installed | loaded | supported | version.

$ afddriverstate installed
AFD-9203: AFD device driver installed status: 'true'

$ afddriverstate loaded
AFD-9205: AFD device driver loaded status: 'true'

$ afddriverstate supported
AFD-9200: Supported

$ afddriverstate version
AFD-9325:     Driver OS kernel version = 3.10.0-862.el7.x86_64.
AFD-9326:     Driver build number = RELEASE.
AFD-9231:     Driver build version = 19.0.0.0.0 (19.24.0.0.0).
AFD-9547:     Driver available build number = RELEASE.
AFD-9232:     Driver available build version = 19.0.0.0.0 (19.24.0.0.0).

To check the ASMFD filtering status:

$ asmcmd afd_state
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'RACNODE1'

Check and list ASMFD disks.

$ asmcmd afd_lsdsk

Oracle ASM Thin Provisioned Attribute

The ASM Thin Provisioned attribute enables or disables the functionality to discard unused storage space after a diskgroup rebalance is completed.

Note: the ASM Thin Provisioned attribute only works with the ASM Filter Driver, setting it on non-ASMFD diskgroups has no effect.

The attribute value can be true to enable or false to disable the functionality. The default value is false.

Oracle ASM informs the storage array which space is no longer used and can be repurposed. If a rebalance is not running, a rebalance can be triggered manually with the ALTER DISKGROUP … REBALANCE SQL statement.

How to check ASM ‘thin_provisioned’ attribute ?

set linesize 250
 
column diskgroup heading 'Diskgroup' format a15
column name heading 'Attribute Name' format a25
column value heading 'Value' format a15
column read_only heading 'Read Only' format a15
 
SELECT
  SUBSTR(dg.name,1,12) AS diskgroup, SUBSTR(a.name,1,24) AS name,
  SUBSTR(a.value,1,24) AS value, read_only 
FROM
  V$ASM_DISKGROUP dg, 
  V$ASM_ATTRIBUTE a 
WHERE
  dg.group_number = a.group_number AND
  a.name NOT LIKE '%template%' AND
  a.name LIKE '%thin%'
/ 

Diskgroup       Attribute Name            Value           Read Only
--------------- ------------------------- --------------- ---------------
DATA2           thin_provisioned          true            N
FRA             thin_provisioned          FALSE           N
OCR             thin_provisioned          FALSE           N
REDO            thin_provisioned          true            N

OR

$ asmcmd lsattr -G FRA | grep thin
thin_provisioned

We can easily change the ‘thin_provisioned’ attribute using ASMCA or using ‘alter disk group <DISK_GROUP> set attribute ‘thin_provisioned’=’TRUE’;’

SQL> alter diskgroup FRA set attribute 'thin_provisioned'='TRUE';
Diskgroup altered.

Since Oracle 18c the phase option includes the keywords WITH or WITHOUT, removing the need to use the underscore parameters, check your version documentation for limitations.

SQL> ALTER DISKGROUP DATA REBALANCE WITHOUT COMPACT;
Diskgroup altered.
SQL > alter diskgroup data2 rebalance with balance compact;
Diskgroup altered.
SQL> ALTER DISKGROUP DATA REBALANCE WITH BALANCE COMPACT POWER 1024 WAIT;
Diskgroup altered.
SQL>select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from gv$asm_operation;

INST_ID OPERA STAT POWER  SOFAR   EST_WORK   EST_RATE EST_MINUTES
------- ----- ---- ----- ------ ---------- ---------- -----------
      2 REBAL WAIT     1
      2 REBAL WAIT     1
      2 REBAL WAIT     1
      1 REBAL RUN      1   1951    8634894       2475        3488
      1 REBAL DONE     1   1017       1017          0           0
      1 REBAL DONE     1      0          0          0           0

6 rows selected.

RMAN-06004: Oracle error from recovery catalog database: RMAN-20005: target database name is ambiguous

When trying to duplicate a database, the following RMAN errors occur:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/29/2025 16:01:38
RMAN-05501: aborting duplication of target database
RMAN-06004: Oracle error from recovery catalog database: RMAN-20005: target database name is ambiguous

RESOLUTION

RMAN> SET DBID=123456789;
RMAN> DUPLICATE DATABASE....

Create a 19c RAC Standby Database From Overnight RMAN Tape Backups

Here is an example of building a RAC standby database by using overnight  NetBackup RMAN backups.

Primary RAC servers: racnode1,racnode2
Standby RAC servers: stdbynode1,stdbynode2
Primary Database Name:  RACTEST
Primary Instance Names: RACTEST1/2
Standby Instance Names: STBTEST1/2

RAC Database version: 19.x.x.x.x

1)On primary database, check most recent RMAN backups are available.

$ rman target /  catalog rman/password@rman
RMAN> list backup of database;

STEP 3 and 4 are not required, if using DUPLICATE.

2) On primary, create a standby controlfile.

SQL> alter database create standby controlfile as '/tmp/RACTEST_standby.ctl';
Database altered.

or

RMAN>  backup device type disk format '/tmp/RACTEST_standby.ctl' current controlfile for standby;

3)Copy standby controlfile onto standby server stdbynode1:/tmp.

[RACTEST1] oracle@racnode1:/tmp$ scp RACTEST_standby.ctl stdbynode1:/tmp/

-4) Copy primary database password file onto standby database server as per “How to copy password file from primary to standby database in 12c onwards“

5) On primary database, make change for FORCE LOGGING, only if database is not in “force logging” yet.

 SQL> ALTER DATABASE FORCE LOGGING;

6)  On primary database,  change or add below parameters .

DB_UNIQUE_NAME=RACTEST
LOG_ARCHIVE_CONFIG='DG_CONFIG=(STBTEST,RACTEST)'
DB_FILE_NAME_CONVERT='STBTEST','RACTEST'
LOG_FILE_NAME_CONVERT='STBTEST','RACTEST'
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=STBTEST
FAL_CLIENT=RACTEST
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
log_archive_dest_1='LOCATION=use_db_recovery_file_dest VALID_FOR=( ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=RACTEST'
LOG_ARCHIVE_DEST_2='SERVICE=STBTEST VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBTEST'

7) On standby database, change or add below parameters.

DB_NAME=RACTEST
DB_UNIQUE_NAME=STBTEST
LOG_ARCHIVE_CONFIG='DG_CONFIG=(RACTEST,STBTEST)'
DB_FILE_NAME_CONVERT='RACTEST','STBTEST'
LOG_FILE_NAME_CONVERT='RACTEST','STBTEST'
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=RACTEST
FAL_CLIENT=STBTEST
*.log_archive_dest_1='LOCATION=use_db_recovery_file_dest VALID_FOR=( ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=STBTEST'
*.LOG_ARCHIVE_DEST_2='SERVICE=RACTEST LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACTEST'

8) On standby database, set standby *.control_files=(‘+DATA1′,’+FRA’), and startup database in nomount mode.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.5366E+10 bytes
Fixed Size 2165488 bytes
Variable Size 7650411792 bytes
Database Buffers 7650410496 bytes
Redo Buffers 63135744 bytes

9) On standby database, restore standby control files. Update parameter “control_files” accordingly.

No need this step if using DUPLICATE.

[STBTEST1] oracle@stdbynode1:/tmp$ rman target /
RMAN> restore controlfile to '+FRA/STBTEST/CONTROLFILE/controlfile2.f' from '/tmp/RACTEST2_standby.ctl';
RMAN> restore controlfile to '+DATA1/STBTEST/CONTROLFILE/controlfile1.f'  from '/tmp/RACTEST2_standby.ctl';
control_files = '+DATA1/STBTEST/CONTROLFILE/controlfile1.f','+fra/STBTEST/CONTROLFILE/controlfile2.f'

10) On standby database, shutdown and mount standby database.

SQL>  create spfile='+DATA1/STBTEST/PARAMETERFILE/spfileSTBTEST.ora' from pfile='/tmp/stbtest.ora';

File created.

$  view $ORACLE_HOME/dbs/initSTBTEST1.ora
$ cat $ORACLE_HOME/dbs/initSTBTEST1.ora
spfile='+DATA1/STBTEST/PARAMETERFILE/spfileSTBTEST.ora' 


SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1.0262E+11 bytes
Fixed Size 2265224 bytes
Variable Size 1.2885E+10 bytes
Database Buffers 8.9657E+10 bytes
Redo Buffers 74416128 bytes

-- no need the following steps if using DUPLICATE
--

SQL> alter database mount standby database;
Database altered.

11) On standby database, restore datafiles from overnight RMAN backups.

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/admin/STBTEST/scripts$ cat restore_STBTEST.rman

run {
allocate channel c1 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate channel c2 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate channel c3 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate channel c4 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
SET NEWNAME FOR DATABASE TO '+DATA1';
restore database;
switch datafile all;
switch tempfile all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/admin/STBTEST/scripts$ nohup rman target / catalog rman/password@rman cmdfile=/u01/app/oracle/admin/STBTEST/scripts/restore_STBTEST.rman log=/u01/app/oracle/admin/STBTEST/scripts/restore_STBTEST.log &
[1] 19448

By using “restore database”, you have to add “switch datafile all” , because the ASM disk path for datafiles and redolog files have changed and they are actually from ones in standby control file, and otherwise the datafile name will be like:

*****************************************
WARNING: The converted filename '+REDO/STBTEST/onlinelog/group_2.280.1163406521'
         is an ASM fully qualified filename.
         Changing the filename to '+REDO/MUST_RENAME_THIS_LOGFILE_2.4294967295.4294967295'.
         Please rename it accordingly.
*****************************************
*****************************************
WARNING: The converted filename '+DATA1/STBTEST/datafile/users.854.1163407487'
        is an ASM fully qualified filename.
         Changing the filename to '+DATA1/MUST_RENAME_THIS_DATAFILE_7.4294967295.4294967295'.
         Please rename it accordingly.
*****************************************

For “MUST_RENAME_THIS_TEMPFILE“, it can be ignored, cos when we “alter database open read only” and “alter pluggable database pdb1 open read only”, those TEMP files are created automatically.

PDB1(3):*****************************************
PDB1(3):WARNING: The converted filename '+DATA1/xxx/34cbb1a7a1fcd520e0630c2101erwdd/tempfile/temp.2591.1175684807'
PDB1(3):         is an ASM fully qualified filename.
PDB1(3):         Changing the filename to '+DATA1/MUST_RENAME_THIS_TEMPFILE_21.4294967295.4294967295'.
PDB1(3):         Please rename it accordingly.

After database and PDB opened read only, DBA can drop and add temp files like :

SQL>  alter tablespace temp drop tempfile 14;

Tablespace altered.


SQL> alter tablespace temp add tempfile '+data1' size 100m autoextend on maxsize 32767m;

Tablespace altered.

OR we can

1)Change Standby_File_Management parameter to MANUAL
2) Cleaning and creating Standby Logfiles.
SQL>ALTER DATABASE DROP LOGFILE GROUP 11
…
..

SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 11 size 1024m;
3)Cleaning and creating Online Logfiles.
SQL>ALTER DATABASE DROP LOGFILE GROUP 1;
SQL>ALTER DATABASE ADD LOGFILE THREAD 1 group 1 size 1024M

4) Cleaning and creating Temp Datafiles.
SQL> ALTER DATABASE RENAME FILE ‘+DATA/MUST_RENAME_THIS_TEMPFILE_1.4294967295.4294967295’ to ‘+DATA/STEST/TEMPFILE/tmpfile_01.dat’;

5) Change Standby_File_Management parameter to AUTO
SQL> alter system set standby_file_management=’AUTO’;

we prefer the following way to duplicate a standby database without much manual works.

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/admin/STBTEST/scripts$ cat duplicate_STBTEST.rman

run {
allocate auxiliary channel c1 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate auxiliary channel c2 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate auxiliary channel c3 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate auxiliary channel c4 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
SET NEWNAME FOR DATABASE TO '+DATA1';
DUPLICATE DATABASE RACTEST FOR STANDBY UNTIL TIME "TO_DATE('19-JAN-2025 14:32:00','DD-MON-YYYYHH24:MI:SS')";
}
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/admin/STBTEST/scripts$ nohup rman target / catalog rman/password@rman cmdfile=/u01/app/oracle/admin/STBTEST/scripts/duplicate_STBTEST.rman log=/u01/app/oracle/admin/STBTEST/scripts/duplicate_STBTEST.log &
[1] 19449

12) On standby database, monitor the progressing by querying gv$session_longops.

SQL> select INST_ID,OPNAME,SOFAR*8/1024/ELAPSED_SECONDS,ELAPSED_SECONDS/60,TIME_REMAINING/60 from gv$session_longops where OPNAME like 'RMAN%' and TIME_REMAINING>6 order by 2;
 INST_ID OPNAME SOFAR*8/1024/ELAPSED_SECONDS ELAPSED_SECONDS/60 TIME_REMAINING/60
---------- ---------------------------------------------------------------- ---------------------------- ------------------ -----------------
 1 RMAN: aggregate input 425.803241 20.1666667 102.85
 1 RMAN: full datafile restore 105.103955 21.55 103.016667
 1 RMAN: full datafile restore 112.092109 21.9 94.7833333
 1 RMAN: full datafile restore 113.487487 21.8833333 93.4333333
 1 RMAN: full datafile restore 108.213722 21.8833333 99.35

13) Restore gap archivelogs.

RMAN>restore archivelog from logseq=100 until logseq=200 thread=1;

RMAN>restore archivelog from logseq=80 until logseq=180 thread=2;

14)  Add standby online logfiles ( n + 1 ) into both primary and standby database.

SQL> alter database add standby logfile thread 1 group 11 size 1024m;

Database altered.
....
...
..
.

15) On standby database,  create spfile if not yet.

SQL> create spfile='+DATA1' from pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initSTBTEST1.ora';

File created.

$ echo spfile='+DATA1/STBTEST/PARAMETERFILE/spfile.1323.925567677' > initSTBTEST1.ora

$ scp initSTBTEST1.ora stdbynode2:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initSTBTEST2.ora

16) On standby database, put standby database under recovery.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;


ORACLE instance started.

Total System Global Area 1.0489E+10 bytes
Fixed Size 2162560 bytes
Variable Size 5704253568 bytes
Database Buffers 4764729344 bytes
Redo Buffers 17899520 bytes

SQL> alter database mount standby database;

Database altered.

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

Database altered.

SQL> alter database recover managed standby database disconnect nodelay;

17) Add standby database into cluster.

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs$ srvctl add database -n STBTEST -o /u01/app/oracle/product/12.1.0/dbhome_1 -d STBTEST -r physical_standby
[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs$ srvctl modify database -d STBTEST -s mount
[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs$ srvctl add instance -d STBTEST -i STBTEST1 -n stdbynode1
[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs$ srvctl add instance -d STBTEST -i STBTEST2 -n stdbynode2

18) Configure archivelog deletion policy for both primary and standby databases.

a) On primary, log on to RMAN and change the archive log deletion policy by running:

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

b) On standby, log on to RMAN and change the archive log deletion policy by running:

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

19) For 12c onwards, no need to add a static registration into LISTENER.ora on all nodes of both primary and standby database. Below is only for under 12c like 11.2.0.4, etc.

#
# DGMGRL>switchover to <standby> Fails with ORA-12514 (Doc ID 1582927.1)
# Each Standby needs a static registration for the switchover to work
#
-- for node racnode1

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = RACTEST1)
 )
 )

-- for node racnode2

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = RACTEST2)
 )
 )

-- for node stdbynode1

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = STBTEST1)
 )
 )

-- for node stdbynode2

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = STBTEST2)
 )
 )


DGMGRL> show instance 'STBTEST1' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBTEST_DGMGRL)(INSTANCE_NAME=STBTEST1)(SERVER=DEDICATED)))'
DGMGRL> show instance 'STBTEST2' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.12)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBTEST_DGMGRL)(INSTANCE_NAME=STBTEST2)(SERVER=DEDICATED)))'
DGMGRL> show instance 'RACTEST1' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.20)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=RACTEST_DGMGRL)(INSTANCE_NAME=RACTEST1)(SERVER=DEDICATED)))'
DGMGRL> show instance 'RACTEST2' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.22)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=RACTEST_DGMGRL)(INSTANCE_NAME=RACTEST2)(SERVER=DEDICATED)))'

Create and enable DG broker

set db_broker_config_file1 and 2 on both primary and standby. remove ‘LOG_ARCHIVE_DEST_2’ on standby 

-- on standby only
SQL> alter system set LOG_ARCHIVE_DEST_2='';
SQL> alter system set dg_broker_config_file1='+DATA2/RACTEST/DATAGUARDCONFIG/dr1RACTEST.dat';

System altered.

SQL> alter system set dg_broker_config_file2='+DATA2/RACTEST/DATAGUARDCONFIG/dr2RACTEST.dat';

System altered.

SQL> alter system set dg_broker_start=true;
DGMGRL> connect sys
Password:
Connected to "RACTEST"
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION DG_RACTEST AS PRIMARY DATABASE IS RACTEST CONNECT IDENTIFIER IS RACTEST;
Configuration "dg_ractest" created with primary database "ractest"

DGMGRL> ADD DATABASE STBTEST AS CONNECT IDENTIFIER is STBTEST MAINTAINED AS PHYSICAL;
Database "stbtest" added

System altered.


DGMGRL> enable configuration;

DGMGRL> show configuration;

13c OEM Information Publisher Reports: Error rendering element. Exception: ORA-00942: table or view does not exist

When using the OEM Reporting System you are limited to access the repository only (MGMT_VIEW). So, the solution is to grant select on schema.table_name to MGMT_VIEW.

You need give  a read permission to MGMT_VIEW.

grant select on sysman.TABLE_NAME to MGMT_VIEW;

This will help you remove the error while reporting the data from OEM Information publisher.