ANALYZED

Analyzed failed with following error out of 2700 tables 2200 odd got analyzed
ERROR at line 1:
ORA-12801: error signaled in parallel query server P052
ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10564
ORA-06512: at "SYS.DBMS_STATS", line 10751
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1

This is known issue in earlier version of oracle 9i when estimate was used instead of compute while gathering statistics using GATHER_SCHEMA_STATS/GATHER_DATABASE_STATS command. Although it was claimed to be fixed in 9.2.0.20 i faced problem in 9.2.0.6.0 while estimating whereas compute worked great.



Analyze query:



Select OWNER,TABLE_NAME,
to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') "last_analyzed"
from dba_tab_columns
where OWNER not in ('SYS','SYSTEM')
and LAST_ANALYZED is not null
and COLUMN_ID=1
and (SYSDATE-LAST_ANALYZED) <>



Simple analyze job script :



#!/bin/sh# Script:

analyze_only.sh

#

# Description: Analyze Database.
# Set the variables

ORACLE_SID=

ORACLE_BASE=

ORACLE_HOME=

LD_LIBRARY_PATH=$ORACLE_HOME/lib

PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID ORACLE_HOME PATH LD_LIBRARY_PATH


date


# Analyze Databasesqlplus -s "/ as sysdba" <<>

set echo off

set feedback off

set heading off

set pagesi 0

set linesize 125

spool analyze_all_output.lst

execute DBMS_STATS.GATHER_SCHEMA_STATS ownname=>'',cascade=>TRUE);

select '-----------------------------------------------------------' from dual;

select ' TABLE_NAME LAST ANALYZED ' from dual;

select '-----------------------------------------------------------' from dual;

select table_name, to_char(last_analyzed, 'DD-MON-YY HH24:MI:SS') from dba_tables where OWNER='' ORDER by table_name;

select '---------------------End of List--------------------------------------' from dual;

select '-----------------------------------------------------------' from dual;

select ' INDEX_NAME LAST ANALYZED ' from dual;

select '-----------------------------------------------------------' from dual;

select index_name, to_char(last_analyzed, 'DD-MON-YY HH24:MI:SS') from dba_indexes where OWNER='' ORDER by index_name;

select '---------------------End of List--------------------------------------' from dual;

exit

EOF
ANALYZELOG="analyze_all_output.lst"

oraerr='cat $ANALYZELOGgrep ORA-wc -l'

if [ $oraerr -gt 0 ]
then echo "Status as on 'date' : Analyze Failed" >> "analyze_all_output.lst"

sqlplus /nolog <

conn aa/bb@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= xxx)))

insert into bkpstats (BKPDATE,SERVER,SRVRIP,DBNAME,BKPTYPE,BKPSTATS) values (SYSDATE,'','','','DB ANALYZE','JOB FAILED');

commit;
EOF
exit;

else

echo "Status as on 'date' : Analyze Successfull" >> "analyze_all_output.lst"
sqlplus /nolog <<>aa/bb@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= xxx)))

insert into bkpstats (BKPDATE,SERVER,SRVRIP,DBNAME,BKPTYPE,BKPSTATS) values (SYSDATE,'','','','DB ANALYZE','JOB SUCCESSFUL');

commit;
EOF
exit;

fi

mailx -s "$ORACLE_SID Analyze is complete." "xx@xx.com,yy@yy.com" < $ date

RMAN CROSSCHECK

CROSSCHECK command verifies if the object available or expired by examining the file in disk or sbt. CROSSCHECK does not delete files that it does not find. It only updates the repository.

Following error registered in log file while it failed to find the controlfile during scheduled backup

Error message:
RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
List of Mismatched objects
==========================
Object Type Filename/Handle
--------------- ---------------------------------------------------
Datafile Copy C:\PROGRAM FILES\OMNIBACK\TMP\CTRL_ODPI.DBF

Examples to crosscheck:

RMAN> CROSSCHECK CONTROFILE;
RMAN> CROSSCHECK BACKUP;
RMAN> CROSSCHECK COPY;
RMAN> CROSSCHECK backup of database;
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;

Query media manager
SBT
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt;
RMAN> CROSSCHECK BACKUP DEVICE TYPE sbt COMPLETED BETWEEN '01-MAY-09' AND '30-JUNE-09';

DISK
RMAN> CROSSCHECK BACKUP DEVICE TYPE DISK COMPLETED BETWEEN '01-MAY-09' AND '30-JUNE-09';

For default channel below command can be used
RMAN> CROSSCHECK BACKUP COMPLETED BETWEEN '01-MAY-09' AND '30-JUNE-09';

Listener

How to Check If the Password is Set to Listener:

From the listener control prompt, issue the command STATUS. If the Security setting is shown as ON, then the password is set for the listener.


LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ANANDA)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
Start Date 05-JUL-2003 15:23:19
Uptime 0 days 6 hr. 24 min. 56 sec
Trace Level off
Security ON
SNMP OFF
Listener Parameter File d:\ora9\network\admin\listener.ora
Listener Log File d:\ora9\network\log\listener.log
Services Summary...
ANANDA has 1 service handler(s)
ANANDA has 2 service handler(s)
ANANDA has 2 service handler(s)
The command completed successfully
LSNRCTL>

Once password is set any administrative command apart from START, STATUS and HELP require password. It's the least security. Password is set by command change_password

LSNRCTL>change_password

Set password for administrative task

LSNRCTL> set password 607CB