Oracle Rollback observation Time

########## #issue 0:db Alert has the following hint, thread 1 cannot allocatete new log, Sequenec 1111

By checking the V$log, found 10 sets of logs, each set of 1G, one hours to produce 10G logs, should be sufficient.

Suspicion is that a huge amount of things that are not submitted cause this error:

Check
SELECT a.used_ublk
From V$transaction A, v$session b
WHERE a.addr = b.taddr

# # # #issue 1 Check the rollback time, using the ALTER system kill session method, observe the method as follows:
Pmon Monitor The Rollback progress, after alter system kill session "Pid,serial", the ospid are still exsist after kill

After kill, the Ospid 14001 still exsit;
LSOF-P 14001 can check process

Problem Description
-------------------

You do not commit your transactions and the session were accidentally
Killed. Your transactions was rolling back and it was taking a long time.
Rollback started hours ago and is still in progress.

Want to know if there are any-to-speed up the process such as using
Cleanup_rollback_entries in the Init.ora and then restarting the database.

Also want to know what would happen if you shutdown the database after 12
Hours of rollback. Would the rollback pick up where it left off?

Sql> SELECT a.used_ublk
From V$transaction A, v$session b
WHERE a.addr = b.taddr and b.sid = 206;

For example:

If used_ublk showed 29,900 hours ago and is now 22,900, it has
Taken hours to rollback 7,000 entries. It'll take approximately
Another hours to complete depending on the types of transactions
That is rolling back.

SELECT used_ublk from V$transaction;

Select Ses.username,ses.sid,substr (Ses.program, 1,) command,tra.used_ublk from v$session ses, v$transaction tra where SES.SADDR = tra.ses_addr;

Select
S.username,
T.XIDUSN,
T.xidslot,
T.XIDSQN,
X.ktuxesiz
From
Sys.x$ktuxe x,
Sys.v_$transaction T,
Sys.v_$session s
where
x.inst_id = Userenv (' Instance ') and
X.ktuxesta = ' ACTIVE ' and
X.ktuxesiz > 1 and
T.xidusn = X.ktuxeusn and
T.xidslot = X.ktuxeslt and
T.XIDSQN = X.ktuxesqn and
S.SADDR = t.ses_addr;

Or use an automated script (taken from the network)

Set Serveroutput on

Declare
Cursor TX is
Select
S.username,
T.XIDUSN,
T.xidslot,
T.XIDSQN,
X.ktuxesiz
From
Sys.x$ktuxe x,
Sys.v_$transaction T,
Sys.v_$session s
where
x.inst_id = Userenv (' Instance ') and
X.ktuxesta = ' ACTIVE ' and
X.ktuxesiz > 1 and
T.xidusn = X.ktuxeusn and
T.xidslot = X.ktuxeslt and
T.XIDSQN = X.ktuxesqn and
S.SADDR = t.ses_addr;
User_name VARCHAR2 (30);
XID_USN number;
Xid_slot number;
XID_SQN number;
USED_UBLK1 number;
USED_UBLK2 number;
Begin
Open TX;
Loop
Fetch TX into user_name, XID_USN, Xid_slot, xid_sqn, USED_UBLK1;
Exit when Tx%notfound;
If Tx%rowcount = 1
Then
Sys.dbms_lock.sleep (10);
End If;
Select
SUM (ktuxesiz)
Into
Used_ublk2
From
Sys.x$ktuxe
where
inst_id = Userenv (' Instance ') and
Ktuxeusn = Xid_usn and
Ktuxeslt = Xid_slot and
KTUXESQN = Xid_sqn and
Ktuxesta = ' ACTIVE ';
If Used_ublk2 < USED_UBLK1
Then
Sys.dbms_output.put_line (
User_name | |
' s transaction ' | |
Xid_usn | | '. ' | |
Xid_slot | | '. ' | |
xid_sqn | |
' 'll finish rolling back at approximately ' | |
To_char (
Sysdate + used_ublk2/(USED_UBLK1-USED_UBLK2)/6/60/24,
' HH24:MI:SS dd-mon-yyyy '
)
);
End If;
End Loop;
If user_name is null
Then
Sys.dbms_output.put_line (' No transactions appear to being rolling back. ');
End If;
End

/db/aa/oradata corresponds to is not this plate VxVM27001

Solution Description
--------------------
There is no way to speed up the rollback.

There is no-it-speed up the rollback process and there are no formula for
Determining how long it would take to complete. It depends on what type of
Undo the application has generated. Some undo may take Little space
Undo block, but could take the awhile to apply.

Can look at used_ublk in v$transaction to estimate how long it is going
to the rollback.

Sql> SELECT a.used_ublk
from V$transaction A, v$session b
WHERE a.addr = b.taddr and B.sid = <SID>;

For example:

If used_ublk showed 29,900 hours ago and was now 22,900, it have
taken hours to R Ollback 7,000 entries. It would take approximately
another hours to complete depending on the types of transactions
that is rolling Back.

Cleanup_rollback_entries determines how long Smon'll be holding onto one
transaction ' s resources. It is affects recovery of transactions in the
background such as after an instance crash. It doesn ' t affect rollback
by the transaction itself.

Rollback would pick up where it left off if you do shutdown after a hours
of Rollback.

Solution Explanation
--------------------

can use v$transaction used_ublk to estimate how long the rollback is
Going to take but there was no formula for this. If you shutdown the
Database after rollback have started, it would begin where it left off.

For Oracle 9i and onwards, check:
sql> SELECT DISTINCT Ktuxesiz
From X$ktuxe
WHERE ktuxecfl= ' DEAD ';

########### Check the rollback time, kill-9 method to kill the process, the observation method is as follows:
Issue 2 Smon Transaction recovery by Smon, the ospid are not exsist.

# # #monitor

Smon process takes over the recovery when

->server process is dead/crashed. The ospid is not exsist.
->instance itself is crashed

3. Speed up Smon transaction recovery
Smon transaction recovery can be controlled using the Fast_start_parallel_rollback parameter

A. Parallel Transaction Recovery:

To enable Parallel recovery mode, set the parameter Fast_start_parallel_rollback to Low/high.

ALTER SYSTEM SET fast_start_parallel_rollback = high
OR
ALTER SYSTEM SET fast_start_parallel_rollback = Low
B. If the parallel recovery is hanging, enable serial recovery:

To enable serial recovery mode, set the parameter Fast_start_parallel_rollback to FALSE.

Ps:the parameter fast_start_parallel_rollback would be effective if Smon does the transaction recovery (generally a fter a instance crash).

Monitor the transaction recovery by Smon
Select USN, state, Undoblockstotal ' total ', undoblocksdone "done", Undoblockstotal-undoblocksdone "ToDo", Decode ( cputime,0, ' unknown ', sysdate+ (((Undoblockstotal-undoblocksdone)/(undoblocksdone/cputime)/86400)) "Estimated time To complete "from v$fast_start_transactions;

USN state total did ToDo estimated time to complete

-------- ---------------- -------- -------- -------- ---------------------------------

->none

# # # #solution:

You may notice this undoblocksdone is not increasing or increases very slowly.

1, stop the parallel rollback, reduce the IO request, quickly improve the system response ability
If you do not have time to wait for the rollback process to complete the rollback operation, you can follow the instructions below.
Finally on Google based on ora_p001, wait for a undo record keyword, found some information, the following information caused my attention:
Oracle engineers first suspect that the temporary tablespace space is not enough, after checking the temporary table space is not enough space, carefully observe the log Discovery redo log files constantly switching, the analysis should be more transactions do not complete the commit or there are more uncommitted transactions to complete the rollback. The problem now is that we don't have much time to wait for all the transactions to complete the rollback or commit. The problem-solving idea is how to end the rollback or commit of these transactions as soon as possible.
1) Check the SPFile file for the settings of the Fast_start_parallel_rollback parameter, and check the result that the G-Net database does not have this parameter set. If not set explicitly, the default value for this parameter is low. Modify the parameter value to False
2) Start the database to the Nomount state: Startup Nomount
3) Modify parameter value: Alter system set Fast_start_parallel_rollback = FALSE Scope=spfile
4) Shutdown immediate close the database
5) startup start
6) See if the parameter is valid: Show parameter Fast_start_parallel_rollback
7) Wait for some time
8) Shutdown immediate database can be closed
2, speed up the rollback speed
Increase the number of parallel rollback processes, set to high when the rollback process =4*CPU. Execute in SQL command-line mode
ALTER SYSTEM SET fast_start_parallel_rollback = high

Refer http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=116213&id=147411

# # # # #issue 3:

Set Lines 200
Set PageSize 2000
Col name for A65
SELECT name,asynch_io from V$datafile f,v$iostat_file I
WHERE F.file#=i.file_no
and Filetype_name= ' Data File ';

Oracle Rollback observation Time