Monday, July 27, 2009

Free version of ASH

Hi,

If you have a performance problem and don't have license to use Active Session History
you can use a free version of it. It is not using a direct SGA access but it is designed by Kyle Hailey
- one of OEM Performance tab designer.

When I read about direct access to X$ table using a oradebug utility on
Miladin Modrakovic's blog I started to wonder if this two technologies can be combined.
Unfortunately I have tested direct access using oradebug and CPU usage is unexpected high on my VM machine and in that case direct access can disrupt our observations.

regards,
Marcin

Wednesday, July 22, 2009

RMAN backup from active database

Hello,
During my test with ASM I have broken my database. I couldn’t restore an ASM structure and I had to recreate database. Because it was a test environment I hadn’t backup of it but I remembered that I hopefully had a standby database. It should be easy to duplicate standby and recreate my test DB once again.
I decided to use a new feature of RMAN to restore database directly from standby. I created a script and I have started a RMAN.

run {
set newname for datafile 1 to '+DATA';
set newname for datafile 2 to '+DATA';
set newname for datafile 3 to '+DATA';
set newname for datafile 4 to '+DATA';
set newname for datafile 5 to '+DATA';
set newname for datafile 6 to '+DATA';
set newname for tempfile 1 to '+DATA';
duplicate target database to PIORO from active database;
}

rman target sys/oracle@piorost auxiliary sys/oracle@pioro cmdfile=dup_back.txt msglog=dup_back.log

What was my surprise when I found following message in log file.

Starting backup at 22-JUL-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=87 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/oradata/pioro/sysaux01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/22/2009 10:01:58
ORA-19504: failed to create file "+DATA/pioro/datafile/sysaux01.211
channel ORA_DISK_1 disabled, job failed on it will be run on another channel
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/22/2009 10:01:58
RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/22/2009 10:01:58
ORA-19504: failed to create file "+DATA/pioro/datafile/sysaux01.211

Recovery Manager complete.


It was very strange because this configuration was running without any problems before my tests.


After few tries with changing a script (adding a db_file_name_convert, changing a new name) I decided to check Metalink – unfortunately there is a note number 787753.1 that this is a bug in 11.1.0.7 and will be fixed in 11.2 ! My test case was a little bit different than Metalink example because my standby DB has been placed on file system and end results was identical. I have to backup a standby to disk than restore it on my test machine.


After that I hit into another strange issue – because I have restored a control file from standby database there were standby logs and online redo logs had a wrong path. But it should be easy, isn’t? Just add new online logs on ASM and drop old one.


SQL> select group#, member from v$logfile;

1 /oracle/oradata/pioro/redo01.log
2 /oracle/oradata/pioro/redo02.log
3 /oracle/oradata/pioro/redo03.log
4 /oracle/oradata/pioro/standby01.log
5 /oracle/oradata/pioro/standby02.log
6 /oracle/oradata/pioro/standby03.log

SQL> alter database add logfile ('+DATA') size 50M;
alter database add logfile ('+DATA') size 50M
*
ERROR at line 1:
ORA-19528: redo logs being cleared may need access to files


Hmmm another nice issue – yes I have cleared logs due to reset logs option but DB is open now and I’m adding a new redo file not dropping it.
Let’s try to drop something


SQL> alter database drop logfile group 6;
alter database drop logfile group 6
*
ERROR at line 1:
ORA-19528: redo logs being cleared may need access to files


I have restarted DB in clear mode but it didn’t help. In my opinion it could be a problem with control file. So I have decided to force DB to recreated a control file using – alter database backup control file to trace – command.
It was a solution – after that I could drop and add log files groups.


SQL> alter database add logfile ('+DATA') size 50M;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.


I’m not sure if similar problem with redo logs appear in previous version of Oracle but main old approach to don’t install a first release of Oracle DB on production systems seems to be still actual and it give me more safety.


regards,
Marcin

Thursday, July 2, 2009

New performance papers from Method R

Cary Millsap public ate a new paper about software performance - Fundamentals of Software Performance Quick Reference Card. Check a other brilliant paper too - Making Friends with the Oracle Database.

regards,
Marcin