Archive

Archive for the ‘Errors’ Category

Oracle – Startup Phases

July 30, 2011 2 comments

There are 5 states an Oracle database goes through during the startup process.

  1.  Shutdown
    The database is shutdown and cannot be connected to by anyone but SYS.
  2. Startup nomount
    During this phase the database reads the initialization parameters from the spfile or pfile. If you get an error at this stage, it’s best to check this file exists, is accessible and contains no errors.
    This phase does not allow access to the database and is usually only done for database creation, or recreation of control files.
  3. Startup mount
    During this phase the database checks the control file, this is a small binary file that records the physical structure of the database.
    If you receive errors during the mount phase, then it is best to check the control file exists.
    This phase allows for certain DBA activities to take place, but does not allow general access to the database.
  4. Startup open
    During this phase the database tries to start in open mode, ready for all users to access. It checks the datafiles exist and any errors at this stage you should check the datafiles.
    This phase can be run in restricted or unrestricted mode, depending on whether you want all users to have access or not.
Advertisements

Oracle – Optimizer stats not being purged

July 28, 2011 1 comment

I’ve recently been monitoring two databases where a high amount of import/exports are taking place. The SYSAUX and SYSTEM tablespaces have been continually growing.

To resolve this I set the stats retention period to 7 days.

SQL> exec dbms_stats.alter_stats_history_retention(7);

I then continued to monitor the database and found that the SYSAUX tablespace was still continuing to grow. When checking the retention period it showed it to be as set, so I reduced it further to 3 days.

SQL>    select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
3

I then tried rebuilding the stats indexes and tables as they would now be fragmented.

SELECT
sum(bytes/1024/1024) Mb,
segment_name,
segment_type
FROM
dba_segments
WHERE
tablespace_name = 'SYSAUX'
AND
segment_type in ('INDEX','TABLE')
GROUP BY
segment_name,
segment_type
ORDER BY Mb;
MB  SEGMENT_NAME                             SEGMENT_TYPE
--  ---------------------------------------  ----------------
2   WRH$_SQLTEXT                             TABLE
2   WRH$_ENQUEUE_STAT_PK                     INDEX
2   WRI$_ADV_PARAMETERS                      TABLE
2   WRH$_SEG_STAT_OBJ_PK                     INDEX
3   WRI$_ADV_PARAMETERS_PK                   INDEX
3   WRH$_SQL_PLAN_PK                         INDEX
3   WRH$_SEG_STAT_OBJ                        TABLE
3   WRH$_ENQUEUE_STAT                        TABLE
3   WRH$_SYSMETRIC_SUMMARY_INDEX             INDEX
4   WRH$_SQL_BIND_METADATA_PK                INDEX
4   WRH$_SQL_BIND_METADATA                   TABLE
6   WRH$_SYSMETRIC_SUMMARY                   TABLE
7   WRH$_SQL_PLAN                            TABLE
8   WRI$_OPTSTAT_TAB_HISTORY                 TABLE
8   I_WRI$_OPTSTAT_TAB_ST                    INDEX
9   I_WRI$_OPTSTAT_H_ST                      INDEX
9   I_WRI$_OPTSTAT_TAB_OBJ#_ST               INDEX
12  I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST           INDEX
12  I_WRI$_OPTSTAT_IND_ST                    INDEX
12  WRI$_OPTSTAT_HISTGRM_HISTORY             TABLE
14  I_WRI$_OPTSTAT_IND_OBJ#_ST               INDEX
20  WRI$_OPTSTAT_IND_HISTORY                 TABLE
306 I_WRI$_OPTSTAT_HH_ST                     INDEX
366 WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE
408 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST            INDEX

To reduce these tables and indexes you can issue the following:

SQL> alter table <table name> move tablespace SYSAUX;
SQL> alter index <index name> rebuild online;

If you are only running standard edition then you can only rebuild indexes offline. Online index rebuild is a feature of Enterprise Edition.

To find out the oldest available stats you can issue the following:

SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
28-JUN-11 00.00.00.000000000 +01:00

To find out a list of how many stats are gathered for each day between the retention the current date and the oldest stats history issue the following:

SQL> select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by  trunc(SAVTIME) order by 1;
TRUNC(SAV COUNT(1)
--------- ----------
28-JUN-11 2920140
29-JUN-11 843683
30-JUN-11 519834
01-JUL-11 958836
02-JUL-11 3158052
03-JUL-11 287
04-JUL-11 1253952
05-JUL-11 732361
06-JUL-11 507186
07-JUL-11 189416
08-JUL-11 2619
09-JUL-11 1491
10-JUL-11 287
11-JUL-11 126324
12-JUL-11 139556
13-JUL-11 181068
14-JUL-11 4832
15-JUL-11 258027
16-JUL-11 1152
17-JUL-11 287
18-JUL-11 27839
21 rows selected.

What has happened here is that the job run by MMON every 24hrs has checked the retention period and tried to run a purge of all stats older than the retention period. As the job has not compeleted within 5 minutes because of the high number of stats collected on each day, the job has given up and rolled back. Therefore the stats are not being purged.

As each day continues the SYSAUX table is continuing to fill up because the job fails each night and cannot purge old stats.

To resolve this we have to issue a manual purge to clear down the old statistics. This can be UNDO tablespace extensive so it’s best to keep an eye on the amount of UNDO being generated. I suggest starting with the oldest and working fowards.

To manually purge the stats issue the following:

SQL> exec dbms_stats.purge_stats(to_date('10-JUL-11','DD-MON-YY'));PL/SQL procedure successfully completed.

SQL> select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by  trunc(SAVTIME) order by 1;
TRUNC(SAVTIME)         COUNT(1)
-------------------- ----------
29-Jun-2011 00:00:00     843683
30-Jun-2011 00:00:00     519834
01-Jul-2011 00:00:00     958836
02-Jul-2011 00:00:00    3158052
03-Jul-2011 00:00:00        287
04-Jul-2011 00:00:00    1253952
05-Jul-2011 00:00:00     732361
06-Jul-2011 00:00:00     507186
07-Jul-2011 00:00:00     189416
08-Jul-2011 00:00:00       2619
09-Jul-2011 00:00:00       1491
10-Jul-2011 00:00:00        287
11-Jul-2011 00:00:00     126324
12-Jul-2011 00:00:00     139556
13-Jul-2011 00:00:00     181068
14-Jul-2011 00:00:00       4832
15-Jul-2011 00:00:00     258027
16-Jul-2011 00:00:00       1152
17-Jul-2011 00:00:00        287
18-Jul-2011 00:00:00      27839
20 rows selected.

Once the amount of stats has been reduced the overnight job should work, alternatively you can create a job to run this similarly to running manually. Using the following code in a scheduled job:

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."PURGE_OPTIMIZER_STATS"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
dbms_stats.purge_stats(sysdate-3);
end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=6;BYMINUTE=0;BYSECOND=0',
start_date => systimestamp at time zone 'Europe/Paris',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'job to purge old optimizer stats',
auto_drop => FALSE,
enabled => TRUE);
END;

Finally you will need to rebuild the indexes and move the tables. To do this you can spool a script to a dmp file and then run the dmp file.

SQL> select 'alter index '||segment_name||' rebuild;' FROM dba_segments where tablespace_name = 'SYSAUX' AND segment_type = 'INDEX';

Edit the file to remove the first and last lines (SQL> SELECT…. and SQL> spool off)

Run the file to rebuild the indexes.

You can then do the same with the tables

SQL> select 'alter table '||segment_name||' move tablespace SYSAUX;' FROM dba_segments where tablespace_name = 'SYSAUX' AND segment_type = 'TABLE';

Then you can re-run the original query, mine produces the following now and my SYSAUX table is only a few hundred MB full.

.6875 WRH$_ENQUEUE_STAT                 TABLE
.75 WRH$_SEG_STAT_OBJ                   TABLE
.8125 WRH$_SYSMETRIC_SUMMARY_INDEX      INDEX
.8125 I_WRI$_OPTSTAT_HH_ST              INDEX
.8125 WRH$_SQL_PLAN_PK                  INDEX
1 WRI$_OPTSTAT_HISTHEAD_HISTORY         TABLE
1 SYS$SERVICE_METRICS_TAB               TABLE
2 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST         INDEX
2 WRH$_SYSMETRIC_SUMMARY                TABLE
2 WRI$_ADV_PARAMETERS                   TABLE
2 WRI$_ADV_PARAMETERS_PK                INDEX
4 WRH$_SQL_PLAN                         TABLE
689 rows selected.

Oracle – Fix unusable indexes

July 27, 2011 1 comment

If you want to find a list of unusable indexes and fix them you can issue the following SQL that will print out a list of alter commands that can be copied and pasted to the SQL command line then executed to fix the indexes.

SQL> select 'alter index '||index_name||' rebuild;' from all_indexes  where status ='UNUSABLE';
'ALTERINDEX'||INDEX_NAME||'REBUILD;'
--------------------------------------------------
alter index I_WRI$_OPTSTAT_HH_ST rebuild;
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild;
alter index I_WRI$_OPTSTAT_H_ST rebuild;
alter index I_WRI$_OPTSTAT_OPR_STIME rebuild;

This will help resolve errors such as the following:

SQL>  exec dbms_stats.purge_stats(to_date('28-JUN-11','DD-MON-YY'));
BEGIN dbms_stats.purge_stats(to_date('28-JUN-11','DD-MON-YY')); END;
*
ERROR at line 1:
ORA-01502: index 'SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST' or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 1100
ORA-06512: at "SYS.DBMS_STATS", line 17203
ORA-06512: at line 1

Oracle – ORA-01502 Index rebuild error

July 26, 2011 1 comment

I was recently trying to clear down the SYSAUX tablespace that had filled with fragmented statistics retention indexes and tables.

However, when I tried to rebuild one of the indexes I received the following error:

SQL> alter index I_WRI$_OPTSTAT_H_ST rebuild;
alter index I_WRI$_OPTSTAT_H_ST rebuild
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST' or partition of such index is in unusable state

I queried the sys.all_indexes table to find which indexes were in an unusable state.

SQL> select index_name, status from all_indexes where status ='UNUSABLE';
INDEX_NAME                     STATUS
------------------------------ --------
I_WRI$_OPTSTAT_IND_OBJ#_ST     UNUSABLE
I_WRI$_OPTSTAT_IND_ST          UNUSABLE
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST UNUSABLE
I_WRI$_OPTSTAT_H_ST            UNUSABLE

To resolve the issue I rebuild the offending index and all was well and good again.

SQL> alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;
Index altered.

Oracle – RMAN 11g List Failure


The 11g version of RMAN has a useful feature for diagnosing and repairing any failures.

Find any database failures:

RMAN> list failure;

Determine manual or automatic repair:

RMAN> advise failure;

From the output of the advise, try to manually fix the failure. If this fails to work, then try the repair command:

RMAN> repair failure;

Further Reading

Oracle® Database Backup and Recovery User’s Guide

Oracle – Logical Standby Dataguard Error


The following error is thrown up when any user other than sys tries to log onto the database. The third and fourth lines of the error are a red herring which made me investigate the package. After comparing the package in the reporting (logical standby) database and finding it to be exactly the same as that in the production database, I investigated the database guard error.

SQL> conn /
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-16224: Database Guard is enabled
ORA-06512: at "OASIS.OASLOGONCHECK_PACKAGE", line 377
ORA-06512: at line 8
Warning: You are no longer connected to ORACLE.

There are three options for dataguard status – all, standby, or none. I queried v$database to see which option was set for my logical standby:

SQL> select guard_status from v$database;
GUARD_S
-------
ALL

Guard_status protects the data from being changed.

  • ALL – All users other than SYS are prevented from making changes to any data in the database.
  • STANDBY – All users other than SYS are prevented from making changes to any database object being maintained by logical standby.
  • NONE – Indicates normal security for all data in the database.

Therefore the users could not log onto the database because the trigger being called on logon was unable to make updates to the database. I updated the guard_status to “standby” and the issue resolved.

SQL> alter database guard standby;
Database altered.
SQL> conn /
Connected.

AIX – Error when installing Fusion Middleware 11.1.1.5.0 on AIX 7.1

July 15, 2011 2 comments

If you get the below error message when going to run the installer for fusion middleware 11.1.1.5.0:

Checking Operating System Certification:
Expected result : One of 5300.08, 6100.02
Actual Result: 7100.00
Check Complete: The overall result of this check is Failed <<<<
Problem : This Oracle Software is not certified on the current operating system.
Recommendation: Make sure you are installing the software on the correct platform.
-Checking recommended operating system patches:
Check complete. The overall result of this check is : Not executed

Then you can either ignore the error by pressing continue, or you can quit and run the installer with the following:

./runInstaller -ignoreSysPrereqs