Oracle DBA - Rajabaskar Blog

How to recreate the AWR Repository ?

2011-12-26T12:56:00.000+05:30

Last week, I have checked the sysaux tablespace and it was growing abnormally. I have checked the v$sysaux_occupants and AWR repository having 23 GB. Our AWR retention period is 60 days, but AWR having more than 60 days data in repository. Due to several reasons, purging operation it’s not working. Refer the below article.

http://dbarajabaskar.blogspot.com/2010/11/wrhactivesessionhistory-table-does-not.html

I am dropped 4 snapshots and it was working fine. But I felt it took lot of time. I tried to drop lot of snapshots and session was hanging.

I referred the metalink and other notes. They were suggested to drop and recreate the automatic workload repository.

How can we drop and recreate the AWR?

My database is running on 11.2.0.2 and Linux server.

Steps:

1. To disable the AWR statistics gathering parameter.

STATISTICS_LEVEL = BASIC - disables the collection of many of the important statistics.

2. If SGA_TARGET is not in “ZERO”, please change the below init parameter temporary while recreating the AWR.

Take the backup of existing pfile or spfile. If database running spfile, to create the pfile using spfile and take the backup.

shared_pool_size =

db_cache_size =

java_pool_size =

large_pool_size =

sga_target=0

statistics_level=basic

If database running on RAC node, you can change the “CLUSTER_DATABASE=FALSE”.

3. Take the invalid objects list on databases.

4. Shutdown and startup the database in RETRICT mode.

SQL> Shut immediate

SQL> Startup RESTRICT

5. DROP and RECREATE the AWR objects

@/rdbms/admin/catnoawr.sql

alter system flush shared_pool;

@/rdbms/admin/catawrtb.sql

--if database running on ORACLE 11g, we should necessary run the below SQL

@/rdbms/admin/execsvrm.sql

6. Using UTLRP.SQL, we can compile the invalid objects.

7. Shutdown the database

8. Startup the database using backup init parameter file. (whatever original values before our drop and recreate the AWR)

I Hope this article helped you. Suggestions are welcome.

Best Regards
RajaBaskar Thangaraj

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

2011-12-06T09:50:00.000+05:30

Daily we received the below information error in alert log on QA/PERF/Production databases. Database running on Linux 64 bit server and version is 11.2.0.2.

Alert log File information:

Mon Nov 28 22:00:10 2011

DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.

Errors in file /u01/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_j000_14552.trc:

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump job.

I have checked the alert log and respective trace files. On Trace files, I found some information alert.

Trace File Information:

DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"owner"','"tablename"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

I have checked metalink notes and got the solutions.

Why we getting this error everyday midnight?

1. While nightly stats gather job (DBMS_STATS) is running and when trying to gather statistics for temporary data pump external tables, we are getting the above error.

2. Temporary Datapump external tables have not been cleaned up properly. These should have been dropped when the DataPump jobs completed.

Note: while datapump running, oracle internally create master table for tracking and putting the datapump status. This master table helps to stop or start the datapump jobs.

Solutions: To clean up the Orphaned datapump jobs.

1. To ensure that there is no datapump jobs running at the same time as the dbms_stats job

2. To Check and cleanup orphaned datapump jobs.

SELECT owner_name, job_name, operation, job_mode,

state, attached_sessions

FROM dba_datapump_jobs

WHERE job_name NOT LIKE 'BIN$%'

ORDER BY 1,2;

3. To identify the external tables.

SQL> conn / as sysdba

Connected.

SQL>

set linesize 200 trimspool on

set pagesize 2000

col owner form a30

col created form a25

col last_ddl_time form a25

col object_name form a30

col object_type form a25

SQL>

select OWNER,OBJECT_NAME,OBJECT_TYPE, status,

to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created

,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time

from dba_objects

where object_name like 'ET$%'

OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME

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

RAJA ET$001A18EF0001 TABLE VALID 18-may-2011 03:15:59 18-may-2011 03:15:59

RAJA ET$009802870001 TABLE VALID 12-may-2011 09:26:44 12-may-2011 09:26:44

RAJA ET$005800090001 TABLE VALID 12-may-2011 09:18:23 12-may-2011 09:18:23

RAJA ET$005800070001 TABLE VALID 12-may-2011 09:16:42 12-may-2011 09:16:42

RAJA ET$004300050001 TABLE VALID 12-may-2011 09:07:09 12-may-2011 09:07:09

RAJA ET$004300030001 TABLE VALID 12-may-2011 09:06:07 12-may-2011 09:06:07

RAJA ET$001802470001 TABLE VALID 12-may-2011 09:05:11 12-may-2011 09:05:11

RAJA ET$000400390001 TABLE VALID 12-may-2011 09:02:55 12-may-2011 09:02:55

8 rows selected.

SQL> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE

from dba_external_tables

order by 1,2

OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_

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

RAJA ET$000400390001 EXP_DIR1 CLOB

RAJA ET$001802470001 EXP_DIR1 CLOB

RAJA ET$001A18EF0001 EXP_DIR1 CLOB

RAJA ET$004300030001 EXP_DIR1 CLOB

RAJA ET$004300050001 EXP_DIR1 CLOB

RAJA ET$005800070001 EXP_DIR1 CLOB

RAJA ET$005800090001 EXP_DIR1 CLOB

RAJA ET$009802870001 EXP_DIR1 CLOB

8 rows selected.

4. To Drop the external temporary datapump tables.

SQL> drop table RAJA.&tbl_name;

Enter value for tbl_name: ET$000400390001