Troubleshooting Materialized View (MV) Problems in PeopleSoft Reporting.
Step1 - Notification: Email will notify PeopleSoft DBA
team on Materialized View issue:
Sample
Email to PeopleSoft DBA’s:
Materialized
Views Refreshes Out of Date as of Thu Sep 10 03:22:03 EDT 2009 on psrptprd
SQL>
select Name, owner, refresh_mode,
2 to_char(last_refresh, 'dd-MON-yyyy hh:mi PM')
last_refresh,
3 to_char(Start_with,'dd-MON-yyyy hh:mi PM')
next_refresh
4 from all_snapshots
5 where start_with < sysdate - 2/24;
NAME OWNER REFRESH_MODE LAST_REFRESH NEXT_REFRESH
------------------------------ ---------------
--------------- -------------------------- --------------------------
MV_PS_PROJ_RESOURCE_PART MVIEW_FIN PERIODIC 09-SEP-2009 11:58 PM 10-SEP-2009 12:58 AM
Step2 – Determining
status of MV: Check status of job to see if it
is broken or failed. If failed column below is > 0 then go to step 3
otherwise if failed column is = 0 and job is not broken go to step 4.
Example:
Determining status of MV_PS_PROJ_RESOURCE_PART
SELECT
SUBSTR(job,1,6) "Job",
SUBSTR(log_user,1,5) "User",
SUBSTR(schema_user,1,5) "Schema",
SUBSTR(To_CHAR(last_date, 'DD.MM.YYY
HH24:MI'),1,16) "Last Date",
SUBSTR(To_CHAR(next_date, 'DD.MM.YYY
HH24:MI'),1,16) "Next Date",
SUBSTR(broken,1,2) "B",
SUBSTR(failures,1,8) "Failed",
SUBSTR(what,1,100) "Refresh"
FROM DBA_JOBS
Where what like '%PS_PROJ_RESOURCE%';
If job is not broken (B=N)
or has not failed (Failed=0). No further action required by DBA except to
check database performance and health - job is still running, go to step 4.
Job User Schema Last Date Next Date B
------ --------------- --------------- ---------------
--------------- -
Failed
--------
Refresh
----------------------------------------------------------------------------
3250 MVIEW MVIEW 10.09.009 05:57 10.09.009 06:57 N
0
dbms_refresh.refresh('"MVIEW_FIN"."MV_PS_PROJ_RESOURCE_PART"');
If job is broken (B=Y) or has failed (Failed>0). Check database alert log to
investigate underlying cause. An attempt to refresh the MV may provide an
Oracle error. Go to step 3.
Job User Schema Last Date Next Date B
------ --------------- --------------- ---------------
--------------- -
Failed
--------
Refresh
----------------------------------------------------------------------------
3250 MVIEW MVIEW 10.09.009 05:57 10.09.009 06:57 Y
1
dbms_refresh.refresh('"MVIEW_FIN"."MV_PS_PROJ_RESOURCE_PART"');
Step3 – MV failed: If job fails it will try multiple times before setting the job status
to broken (N). Depending on the state of
the job error when you first encounter it you may be require to break the job
manually if it hasn't been broken already and kill related session. Ensure that you allow sessions to rollback
completely otherwise a full refresh may hang.
Example
1: Check on job status for MV_PS_PROJ_RESOURCE_PART.
SQL> SELECT a.job, a.sid, what,
TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') now,
TO_CHAR(a.this_date, 'mm/dd/yyyy hh24:mi:ss')
this
FROM
dba_jobs_running a, dba_jobs b
WHERE
a.job = b.job and a.job=3250;
no rows selected
(no sessions to kill).
Example 2: Active session - kill session and monitor
rollback to completion.
SELECT a.sid, a.serial#, a.username, b.xidusn,
b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr and a.sid=71;
SQL> SELECT a.sid, a.serial#, a.username,
b.xidusn, b.used_urec, b.used_ublk
2 FROM v$session a, v$transaction b
3 WHERE a.saddr = b.ses_addr and a.sid=71;
SID SERIAL# USERNAME XIDUSN USED_UREC
---------- ---------- ------------------------------
---------- ----------
USED_UBLK
----------
71 6720 MVIEW_FIN 119 159383334
4699036
The result of the about query should return “no
rows selected” for a success rollback of the transaction for the session killed.
Next test whether a fast refresh is possible. Test fast refresh of MV_PS_PROJ_RESOURCE_PART
to see if you encounter invalid ROWID error.
If you encounter "ORA-12008:
error in materialized view refresh path" then a full refresh is required.
Example:
Test for fast fresh of MV_PS_PROJ_RESOURCE_PART.
SQL> conn mview_fin/xxxxxxx@psrptprd
Connected.
SQL> EXECUTE
DBMS_MVIEW.REFRESH('MV_PS_PROJ_RESOURCE_PART','F');
BEGIN DBMS_MVIEW.REFRESH('MV_PS_PROJ_RESOURCE_PART','F');
END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh
path
ORA-01410: invalid ROWID
ORA-02063: preceding line from FINANCE
ORA-06512: at "SYS.DBMS_SNAPSHOT",
line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line
2751
ORA-06512: at "SYS.DBMS_SNAPSHOT",
line 2720
ORA-06512: at line 1
Finally check alert log for ora-600 errors that indicate a shared memory
errors and flush the shared pool three times to clear this. Run full refresh and notify Greg Noble; Tripurari
Sinha; Gareth Hale; Manish Verma. Full
Refresh usually takes 5 and 1/2 hours for MV_PS_PROJ_RESOURCE_PART.
Example:
SQL> conn mview_fin/xxxxxxx@psrptprd
Connected.
SQL> EXECUTE
DBMS_MVIEW.REFRESH('MV_PS_PROJ_RESOURCE_PART','COMPLETE',atomic_refresh=>FALSE);
Ensure that statistics is gathered for the
complete mview refresh.
Example:
exec dbms_stats.gather_table_stats('MVIEW_FIN',
'MV_PS_PROJ_RESOURCE_PART', CASCADE=>TRUE);
Step 4 – No major
action required, MV is running and has not failed or broken: Check status of MV job again to ensure job has not broken and is
stilling running. Also, check the general
health of master and reporting databases
(by checking the alert log, MV log and undo tablepaces and database performance
(focusing on the shared_pool) to ensure that there are no general issues with
the databases such lack of database memory or lack of space on key tablespaces
or issues with the MV log.
Example: MV_PS_PROJ_RESOURCE_PART job has not
failed or broken.
SELECT
SUBSTR(job,1,6) "Job",
SUBSTR(log_user,1,5) "User",
SUBSTR(schema_user,1,5) "Schema",
SUBSTR(To_CHAR(last_date, 'DD.MM.YYY
HH24:MI'),1,16) "Last Date",
SUBSTR(To_CHAR(next_date, 'DD.MM.YYY
HH24:MI'),1,16) "Next Date",
SUBSTR(broken,1,2) "B",
SUBSTR(failures,1,8) "Failed",
SUBSTR(what,1,100) "Refresh"
FROM DBA_JOBS
Where what like '%PS_PROJ_RESOURCE%';
Job
User Schema Last Date Next Date B
------ --------------- ---------------
--------------- --------------- -
Failed
--------
Refresh
--------------------------------------------------------------------------------
3250
MVIEW MVIEW 25.09.009 02:24 25.09.009 03:24 N
0
dbms_refresh.refresh('"MVIEW_FIN"."MV_PS_PROJ_RESOURCE_PART"');
Example: MV_PS_PROJ_RESOURCE_PART job is still
running.
oracle >SQL> SELECT a.job, a.sid, what,
TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') now,
TO_CHAR(a.this_date, 'mm/dd/yyyy hh24:mi:ss')
this
FROM
dba_jobs_running a, dba_jobs b
WHERE
a.job = b.job and a.job=3250;
JOB SID
---------- ----------
WHAT
--------------------------------------------------------------------------------
NOW THIS
------------------- -------------------
3250 123
dbms_refresh.refresh('"MVIEW_FIN"."MV_PS_CA_ACCTG_LN_PC_PART"');
09/25/2009 06:35:52 09/25/2009 03:24:17
Appendix
For
a complete refresh (using dbms_mview.refresh) sys.snap$ and sys.mlog$ are
updated to reflect the time of the refresh. The materialized base view is
deleted and all rows selected from the master table are inserted into the
snapshot base table and then sys.slog$ is updated to reflect the time of the
refresh.
For
a fast refresh, the steps are sys.snap$ and sys.mlog$ are updated to reflect
the time of the refresh. Rows in the
materialized base view are deleted and all rows selected from the master table
are inserted into the snapshot base table and then sys.slog$ is updated to
reflect the time of the refresh. Rows
that are not needed anymore for a refresh by any materialized view are deleted
from the materialized view log (e.g. SYSFS.MLOG$_PS_PROJ_RESOURCE).
A
long running refresh will have a PC_REPORTS_USER session in the F90xxx or
H90xxx databases and a long running SQL operation for that session normally
gives an indication of how long the refresh will take to complete. For a full refresh there may be an automatic
fast refresh after the full refresh that gets the materialized view up to the
current time.
See below for examples of fast and complete
refresh for MV_PS_PROJ_RESOURCE_PART.
Consider using atomic_refresh=FALSE option for complete refresh – the
default for Oracle 11g is atomic_refresh=TRUE (DELETE instead of a TRUNCATE).
Example:
EXECUTE
DBMS_MVIEW.REFRESH('MV_PS_PROJ_RESOURCE_PART','F'); -- fast refresh
EXECUTE
DBMS_MVIEW.REFRESH('MV_PS_PROJ_RESOURCE_PART','COMPLETE',atomic_refresh=>FALSE);
-- complete refresh by truncating MV.
Use
DBMS_JOB and ALTER MATERIALIZED VIEW statements to manage and schedule MV jobs.
Example: MV_PS_PROJ_RESOURCE_PART is
scheduled to run every hour.
exec dbms_job.broken(3250, FALSE);
ALTER MATERIALIZED VIEW
MVIEW_FIN.MV_PS_PROJ_RESOURCE_PART
REFRESH
NEXT sysdate + 1/24;
Ensure that
statistics is gathered for complete refresh.
Example:
exec dbms_stats.gather_table_stats('MVIEW_FIN',
'MV_PS_PROJ_RESOURCE_PART', CASCADE=>TRUE);
Thanks Thomas.
How to Create a New Materialized View.
In
F8XPRD
create
materialized view log on sysfs.PS_XXXXX tablespace
with
rowid, sequence excluding new values;
grant
select on sysfs.PS_XXXXX to
pc_reports_user;
grant
select on sysfs.MLOG$_PS_XXXXX to pc_reports_user;
in
PSRPTPRD as Mview_F8X
DROP
MATERIALIZED VIEW MVIEW_F8X.MV_PS_XXXXX;
CREATE
MATERIALIZED VIEW MVIEW_F8X.MV_PS_XXXXX
TABLESPACE
MVIEW_F8X
NOCACHE
NOLOGGING
COMPRESS
NOPARALLEL
BUILD
IMMEDIATE
USING
INDEX
REFRESH
FAST
START
WITH sysdate
NEXT
SYSDATE + 1/24
WITH
ROWID
AS
SELECT
* FROM "SYSFS"."PS_XXXX"@F8X "PS_XXXXX";
CREATE
INDEX MView_F8X.MV_PS_XXXXX_IX1 ON MView_F8X.MV_PS_XXXXX
(COL1,
COL2, COL3)
LOGGING
TABLESPACE
PSINDEX
NOPARALLEL;
ALTER
TABLE MVIEW_F8X.MV_PS_XXXXX ADD (
CONSTRAINT PS_XXXXX_PK
UNIQUE (COL1, COL2, COL3)
DEFERRABLE INITIALLY IMMEDIATE);
GRANT
SELECT ON MVIEW_F8X.MV_PS_XXXXX TO SYSFS;
DROP
PUBLIC SYNONYM PS_XXXXX;
CREATE
PUBLIC SYNONYM PS_XXXXX FOR MVIEW_F8X.MV_PS_XXXXX;
Materialized View Monitoring
The
check is simply to ensure that the Materialized view has been refreshed within
2 hours of its scheduled time by checking all_snapshots.
select Name, owner, refresh_mode,
to_char(last_refresh, 'dd-MON-yyyy hh:mi PM') last_refresh,
to_char(Start_with,'dd-MON-yyyy hh:mi PM') next_refresh
from all_snapshots
where start_with < sysdate - 2/24;