ORA-19852: Error creating services for auxiliary instance

This error  generally occur when we go for TSPIR(tablespace point-in-time recovery) . When we issue the "recover tablespace ... "  command we get this error as

RMAN> recover tablespace tspir  until scn 5659022 auxiliary destination 'F:\';
Starting recover at 30-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='tAbD'
RMAN-00571: ========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===========
RMAN-00571: ========================================================
RMAN-03002:failure of recover command at 11/30/2011 14:56:13
ORA-19852:error creating services for auxiliary instance tAbD (error 0)
ORA-27302: failure occurred at:
ORA-27303:additional information: failed to start instance

This error occur because the previous failed TSPITR attempt wasn't cleaned up properly . Therefore to solve this issue, we have to clean the failed TSPIR attempts .Issue the following procedure to clean up the previous failed auxiliary creation attempt:

SQL> exec dbms_backup_restore.manageauxinstance ('TSPITR',1) ;
PL/SQL procedure successfully completed.



Enjoy      :-) 

ORA-7445 Internal Error

An ORA-7445 is a generic error, and can occur from anywhere in the Oracle code. The precise location of the error is identified by the core file and/or trace file it produces. Whenever an ORA-7445 error is raised a core file is generated. There may be a trace file generated with the error as well.

Prior to 11g, the core files are located in the CORE_DUMP_DEST directory. Starting with 11g, there is a new advanced fault diagnosability infrastructure to manage trace data. Diagnostic files are written into a root directory for all diagnostic data called the ADR home. Core files at 11g will go to the ADR HOME/cdump directory

For more Indispensability check the below :

1. Check the Alert Log  :    The alert log may indicate additional errors or other internal errors at the time of the problem. In some cases, the ORA-7445 error will occur along with ORA-600, ORA-3113, ORA-4030 errors. The ORA-7445 error can be side effects of the other problems and we should review the first error and associated core file or trace file and work down the list of errors. 

If the ORA-7445 errors are not associated with other error conditions, ensure the trace data is not truncated. If we see a message at the end of the file “MAX DUMP FILE SIZE EXCEEDED" . the MAX_DUMP_FILE_SIZE parameter is not setup high enough or to ‘unlimited’. There could be vital diagnostic information missing in the file and discovering the root issue may be very difficult. Set the MAX_DUMP_FILE_SIZE appropriately and regenerate the error for complete trace information.

2. Search 600/7445 Lookup Tool  :  Visit My Oracle Support to access the ORA-00600 Lookup tool (Note 7445.1). The ORA-600/ORA-7445 Lookup tool may lead you to applicable content in My Oracle Support on the problem and can be used to investigate the problem with argument data from the error message or we  can pull out key stack pointers from the associated trace file to match up against known bugs.

3. “Fine tune” searches in Knowledge Base  :   As the ORA-7445 error indicates an unhandled exception in the Oracle source code, our search in the Oracle Knowledge Base will need to focus on the stack data from the core file or the trace file.

Keep in mind that searches on generic argument data will bring back a large result set . The more we can learn about the environment and code leading to the errors, the easier it will be to narrow the hit list to match our problem.

4  .  If assistance is required from Oracle  :    Should it become necessary to get assistance from Oracle Support on an ORA-7445 problem, please provide at a minimum, the 

• Alert log
• Associated tracefile(s) or incident package at 11g
• Patch level information
• Core file(s)
• Information about changes in configuration and/or application prior to issues
• If error is reproducible, a self-contained reproducible testcase: Note.232963.1 How to Build a Testcase for Oracle Data Server Support to Reproduce ORA-600 and ORA-7445 Errors.
• RDA report or Oracle Configuration Manager information

Enjoy     :-) 

Estimate the Optimal UNDO Tablespace size in Oracle

We can size the undo tablespace appropriately either by using automatic extension of the undo tablespace or by using the Undo Advisor for a fixed sized tablespace.If we have decided on a fixed-size undo tablespace, the Undo Advisor can help us to estimate needed capacity.The Undo Advisor relies for its analysis on data collected in the Automatic Workload Repository (AWR). It is therefore important that the AWR have adequate workload statistics available so that the Undo Advisor can make accurate recommendations. 

Below is the script which will estiate the size of the undo tablespace required and also provide the detail when the database face the Number of "ORA-01555 (Snapshot too old)" encountered since the last startup of the instance 

SQL> SELECT  d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]“,
                        SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]“,
                       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
                      g.undo_block_per_sec) / (1024*1024)  “NEEDED UNDO SIZE [MByte]“
  FROM        (SELECT SUM(a.bytes) undo_size  FROM v$datafile a,  v$tablespace b,
                     dba_tablespaces c
 WHERE      c.contents = 'UNDO'
 AND          c.status = 'ONLINE'
 AND          b.name = c.tablespace_name
AND           a.ts# = b.ts#  ) d, v$parameter e, v$parameter f,
                 ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24))  undo_block_per_sec
FROM       v$undostat  ) g 
WHERE      e.name = 'undo_retention'
AND         f.name = 'db_block_size'
/
set feedback off
set heading off
set lines 132
declare
  cursor get_undo_stat is
         select d.undo_size/(1024*1024) "C1",
                substr(e.value,1,25)    "C2",
                (to_number(e.value) * to_number(f.value) *
g.undo_block_per_sec) / (1024*1024) "C3",
                round((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec)))             "C4"
           from (select sum(a.bytes) undo_size
                   from v$datafile      a,
                        v$tablespace    b,
                        dba_tablespaces c
                  where c.contents = 'UNDO'
                    and c.status = 'ONLINE'
                    and b.name = c.tablespace_name
                    and a.ts# = b.ts#)  d,
                v$parameter e,
                v$parameter f,
                (select max(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec from v$undostat)  g
          where e.name = 'undo_retention'
            and f.name = 'db_block_size';
begin
dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) ||
'To optimize UNDO we have two choices :');
dbms_output.put_line('==========================================' || chr(10));
  for rec1 in get_undo_stat loop
      dbms_output.put_line('A)
Adjust UNDO tablespace size according to UNDO_RETENTION :'
|| chr(10));
      dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',65,'.')|| ' : ' ||
TO_CHAR(rec1.c1,'999999') || ' MEGS');
      dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION
(' || ltrim(TO_CHAR(rec1.c2/60,'999999'))
 || ' MINUTES)
',65,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MEGS');
      dbms_output.put_line(chr(10));
      dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :'
|| chr(10));
      dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',65,'.') ||
' : ' || TO_CHAR(rec1.c2/60,'999999') || ' MINUTES');
      dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE
(' || ltrim(TO_CHAR(rec1.c1,'999999')) || ' MEGS) ',65,'.') || ' : ' ||
TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');
  end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/
select 'Number of "ORA-01555 (Snapshot too old)" encountered since
the last startup of the instance : ' || sum(ssolderrcnt)
from v$undostat;


Enjoy   :-)