TIP 79 : Simple and effective backup script.

How many times you have seen different backup script with different commands ? Have you ever asked if all consider Oracle best practices and what is pros and cons of each ?
Here, I am trying to focus on some Oracle best recommendations in terms of backup/recovery and then at the end represent a sample RMAN backup script which takes into account all those recommendations

Recommendations :

- Check logical corruption to make sure backup is good.
- Put full database backup as incremental level 0 so it is considered in incremental backup/recovery scenario.
- Make sure that backup pieces have time-stamp to overcome look-up performance issue when recovery catalog is backup.
- Make sure to have each datafile in a single backup piece so for partial recovery RMAN goes through only one piece.
- Make sure to have unique name for each backup piece so RMAN does not overwrite backup pieces if for any reason backup is taken more often.
- Tag backups properly to make searching them and restoring them easier.
- Keep DBID in controlfile backup piece in order to save time to find it when it is required.
- Take current controlfile backup although autobackup is ON,that way it guarantees that always controlfile backup is there.

Based on the above, the following is the standard template that covers all features.


run
{
allocate channel t1 type disk;
BACKUP AS COMPRESSED BACKUPSET check logical INCREMENTAL LEVEL 0 DATABASE filesperset 1 plus archivelog format '/mnt/u05/backuptest/backup_%d_set%s_piece%p_copy%c_%T_%U' TAG = DB_BACK_FULL_DAILY ;
backup current controlfile format '/mnt/u05/backuptest/backup_controlfile_%d_DBID%I_%T_%U.ctl' TAG=CTL_BACK_FULL_DAILY;
backup spfile format '/mnt/u05/backuptest/backup_parameter_%d_DBID%I_%T_%U.ctl' TAG=PARAM_BACK_FULL_DAILY ;
delete obsolete;
release channel t1;
}

TIP #78:Database server gets freezed after increasing memory foot print.

I have a client which went through a switchover practice for the first time.
After switching over to a standby box, I realized a performance issue in terms of IO write.As the result, it was decided to bump up database memory footprint for SGA and PGA since the box has enough memory.
Changes were made on spfile and new SGA was bumped up to 24GB and PGA to 4GB from total 32GB. After bouncing database, database hung in nomount and after a minute,the box was totally freezed which did not allow connection anymore.
The following was reported in Alert log :



ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3



According to Oracle (Metalink note 560309.1), This could be lack of memory or improper setting of swap. Since in my case, physical memory was enough, it turned out that the issue was because of improper setting of swap. Swap needs to be set at least 0.75 times of physical RAM when memory>8GB

In my case, swap was 16GB while physical memory was 32GB which explained the case.
This issue could happen on any platform, my case was on Sun Solaris 64bit.

TIP #77 : OC4J failed to start after applying Oct2008 security patch

After applying Oct2008 CPU patch for a client on Oracle application server 10gR2, OC4J_Security on Infra structure tier failed to startup. No much information in logs, the only thing that was shown after couple minutes was :


ias-component/process-type/process-set:
OC4J/OC4J_SECURITY/default_island

Error
--> Process (pid=0)
oid dependency failed
OID
failed to start a managed process because a dependency check failed
Log:
none

It was interesting that OID started without any issue.
Above issue could occur when OID run on port lower than 1024 and $ORACLE_HOME/bin/oidldapd has not the right permission.
This file should own by root.To fix permission issue, $ORACLE_HOME/root.sh from Infra structure home should be run.

Happy CPU patching !

TIP 76#: How to find peeked value of bind variables

Oracle usually recommends to be sensitive in using bind variables. Starting Oralce 9i, Oracle introduced a concept of bind variable peeking. This means that the first time a SQL statement containing bind predicates is parsed, the optimizer will look at the value of the bind variable and use that value for creating the execution plan for the query.
This plan is then stored and used for all future executions regardless of changed bind values. If the initial bind value is not representative of the other values that are provided in future executions of the query, then, although the access path is good for the intial value, future executions may perform poorly. This may result in suboptimal query performance for some iterations of the query.

As I said, bind variable peeking is TRUE by default in 9.0 and above. This feature could be disabled with setting _OPTIM_PEEK_USER_BINDS to FALSE.

In this post, I am trying to demonstrate how to find the peeked value for bind variable, This helps in the case of poor performance to see if Oracle execute query based on the right value. (Sample was run in 10.2.0.3)

Test 1
In this test, a query with bind variable was run against test table.
When bind_data column in v$sql is not null,SQL has bind variables.
v$sql_bind_capture is used to find which bind value has been peeked by Oracle.



Test 2
In this test, new value (100) was passed to the same query but as it is shown Oracle still has -1 as a peek value.


Test 3
In this test, shared pool was flushed from test table. This forced Oracle to peek again the value. This time it peeked 100.