[EN] Cleaning SYSAUX Optimizer Statistics History

When new optimizer statistics collected for a database object, old statisitcs are sent to SYSAUX tablespace. This information is stored in "Server Manageability - Optimizer Statistics History" component of SYSAUX tablespace.

By this feature we can use DBMS_STATS.RESTORE... procedures.

The default retention for Optimizer Statistics History is 31 days. Old information are automaticly deleted by Oracle Database. Without any manual intervention.

But sometimes this 31 day retention may be too much. In one of my database this component was 75 GB.

--Check SYSAUX components and their sizes.

select * from v$sysaux_occupants order by space_usage_kbytes desc;

[EN] Sending HTML UTF-8 e-mail via UTL_SMTP

You can directly use SYS.UTL_SMTP package from your e-mail sending code. But for ease of use i recommend creating the following procedure. Because if you use UTL_SMTP directly from your code, you have to write UTL_SMTP package parameters again and again. With this following procedure you can easily send e-mails from your e-mail sending code.

Please carefully examine and change parts specific to your site on the following code. Like "smtp_hostname","smtp_port".

[EN] FAL[client]: Failed to request gap sequence ... FAL[client]: All defined FAL servers have been attempted.

Problem:

Because of a network problem one of our physical standby database, there was some gaps.

...
      9597          2 +ADATA/astb/archivelog/2015_11_09/thread_2_seq_9597.1827.895359343
      9600          2 +ADATA/astb/archivelog/2015_11_09/thread_2_seq_9600.1828.895360629
      9601          2 +ADATA/astb/archivelog/2015_11_09/thread_2_seq_9601.1831.895361495
      9604          2 +ADATA/astb/archivelog/2015_11_09/thread_2_seq_9604.1825.895363191
      9607          2 +ADATA/astb/archivelog/2015_11_10/thread_2_seq_9607.1824.895364061
      9608          2 +ADATA/astb/archivelog/2015_11_10/thread_2_seq_9608.1823.895364505
      9609          2 +ADATA/astb/archivelog/2015_11_10/thread_2_seq_9609.1826.895365409
...

After the network problem was solved by our system team, Standby RFS processes can succesfully get recent archived logs. But could not get earlier gap archived logs. Even not try to get them. We monitor this via V$MANAGED_STANDBY view.

When we try to restart media recovery on the standby site. We still see the following alertlog warnings.

[EN] Argument list too long

PROBLEM:

/u01/app/oracle/product/11.2.0/grid/rdbms/audit $ rm *.aud
-bash: /bin/rm: Argument list too long

SOLUTION:

/u01/app/oracle/product/11.2.0/grid/rdbms/audit $ ls -la | grep ".aud" | awk {'print "rm "$9'} > rm_audit_log.sh
ksh rm_audit_log.sh

[EN] Who is locking my user ?

Use the following steps, if someone is locking an oracle database user by entering wrong password.
Wrong password attemp limit is determined by FAILED_LOGIN_ATTEMPTS profile parameter.
So check it first. In our case we assume that it's value is 3.

ORA-01017: invalid username/password; logon dedied
ORA-01017: invalid username/password; logon dedied
ORA-01017: invalid username/password; logon dedied
ORA-28000: the account is locked

[EN] ORA-01111: name for data file n is unknown - rename to correct file

PROBLEM:

On one of our physical standby database, we noticed that media recovery was stooped.
When we try to start it again we got the following errors in altertlog;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (PRODSTB)
Fri Aug 17 09:11:05 2015
MRP0 started with pid=39, OS id=8061128
MRP0: Background Managed Standby Recovery process started (PRODSTB)
started logmerger process
Fri Aug 17 09:11:10 2015
Managed Standby Recovery starting Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/prodstb/PRODSTB/trace/PRODSTB_pr00_7985076.trc:
ORA-01111: name for data file 24 is unknown - rename to correct file
ORA-01110: data file 24: '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00024'
ORA-01157: cannot identify/lock data file 24 - see DBWR trace file
ORA-01111: name for data file 24 is unknown - rename to correct file
ORA-01110: data file 24: '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00024'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (PRODSTB)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION