How much of redo log file has been used

It is useful to be able to see what is happening to a redo log file, especially if this is large. The scripts below indicates the percentage of the log file used

col Used format 99,999,999 heading 'BytesinCurrtRedoLog'
select cpodr_bno Used from sys.x$kcccp;
or
select le.leseq CURRENT_LOG_SEQUENCE#,
100*cp.cpodr_bno/LE.lesiz PERCENTAGE_FULL
from
sys.x$kcccp cp,sys.x$kccle le
WHERE
LE.leseq =CP.cpodr_seq

 

Is a transaction rolling back and how long will it take

There are various ways of seeing what is happening within a transaction and whether it is rolling back or not.

Probably the simplest is to monitor the used_blk column in v$transaction and see if the value is reducing. Used_blks indicates the number of undo blocks allocated to a transaction so any reduction shows that rollback is in progress. The relevant sql is below

select a.sid,b.used_ublk
from v$session a, v$transaction b
where a.taddr = b.addr and a.username = 'PROD' ;

A logical step then is to determine how many blocks there are to rollback and and how long it is taking. A query of the format

(used_blocks_at_9:00 / used_blocks_at_8:59 - used_blocks_at_9:00))

will give a reasonably good view of how long rollback will take to complete (in minutes in this example)

 

ORA-24005 when dropping a schema and DEF$_AQCALL / DEF$_AQERROR tables mentioned

When attempting to drop a schema the failure ORA-24005 occurs referring to the DEF$_AQCALL and DEF$_AQERROR tables.

It suggests using the “execute DBMS_AQADM.DROP_QUEUE_TABLE (table_name, force, auto_commit);” procedure. This fails.

The workround is to log into svrmgr or sqlplus with DBA privileges and issue the command

alter session set events ‘10851 trace name context forever, level 2’; syntax

Then drop the relevant queue table(s)

See metalink note 1016601.102 for further information and note 100001.1 for more on when the catqueue.sql has been run as system rather than sys.

How much longer a job has left to run

The v$session_longops view can be used to determine how long a job has to run


column "% Done" format 999.99
column opname format a15
column sql_text format a70
column "T Left" format 99999
select a.sid,(a.sofar/a.totalwork)*100 "% Done",
to_char(a.last_update_time, 'HH24:MI:SS'),
a.username,a.time_remaining "Time Left",
a.opname ,s.sql_text
from v$session_longops a, v$session b, v$sqltext s

where a.sid =b.sid and b.sql_address = s.address

and a.sofar <> a.totalwork

order by b.sid, last_update_time

/

Convert numbers to words - a function to do the work

I pulled this from the Oracle-L mailing list a while ago. The original poster was not credited I am afraid.

create or replace
function spell_number( p_number in number )
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray( '',
' thousand ', ' million ',
' billion ', ' trillion ',
' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',
' octillion ', ' nonillion ',
' decillion ', ' undecillion ',
' duodecillion ' );

l_num varchar2(50) default trunc( p_number );
l_return varchar2(4000);
begin
for i in 1 .. l_str.count
loop
exit when l_num is null;

if ( substr(l_num, length(l_num)-2, 3) <> 0 )
then
l_return := to_char(
to_date(substr(l_num, length(l_num)-2, 3),'J' ),
'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-3 );
end loop;

return l_return;
end;
/
SQL> select
2 spell_number( 12345678901234567890123456789012345678 )
3 from dual;

SPELL_NUMBER(1234567890123456789012345678901234567
--------------------------------------------------
Twelve undecillion Three Hundred Forty-Five decillion Six Hundred Seventy-Eight nonillion
Nine Hundred One octillion Two Hundred Thirty-Four septillion Five Hundred Sixty-Seven sextillion
Eight Hundred Ninety quintillion One Hundred Twenty-Three quadrillion Four Hundred Fifty-Six trillion
Seven Hundred Eighty-Nine billion Twelve million Three Hundred Forty-Five thousand Six Hundred Seventy-Eight

SPFILE - how to set a parameter to it's default value

Alter system reset sga_max_size scope=spfile sid='*'

SPFILE - how to start a database with invalid parameters in the spfile

It is possible to be unable to start a database using a spfile because there are mutually incompatible parameters within the spfile. To get around this, shut the database down and perform a strings command on the binary spfile to get all the readable entries out. Create an initSID.ora file from the strings command, removing the invalid entries and start the database using that file. Once successfully started with the correct entries then create a new spfile with the 'create spfile=xx from pfile=yyy' command

However Jared Still pointed out an alternative approach, which I must admit works and is much easier.

SQL> alter system set undo_management=bogus scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-30043: Invalid value 'BOGUS' specified for parameter 'Undo_Management'

SQL> create pfile='c:\temp\pfile.ora' from spfile;
File created.

SQL> !wordpad c:\temp\pfile.ora ( here I edited the pfile just created
to change BOGUS back to AUTO)

SQL> create spfile from pfile='c:\temp\pfile.ora';
File created.

SQL> startup
ORACLE instance started.
RMAN Backup and Recovery Testing Strategy

The document describes a series of tests which should be undertaken to prove that datafiles, archivelog files and control files can be archived to and recovered from tape using the RMAN catalogue and a suitable tape backup system such as Networker

Download document in Word format

How to write to the alert log file

There is a way to write alerts to the alert log. This could be a good way to help with testing by generating alerts

SQL> execute dbms_system.ksdwrt(2, 'TEST --- write to log');
or with a time-stamp as well
SQL> execute sys.dbms_system.ksdwrt(2,to_char(sysdate)|| ' -- ');

The first parameter can be 1,2 or 3. 1 writes to the alert log, 2 writes to the trace file and 3 writes to both

 

 

 

TOP

Rowing1