SLTechnology News&Howtos › Database ›

Oracle 9i switch logfile hang

Shulou Source: shulou.com Published: 2022-06-01 06:14:25 10月03日 Update

Phenomenon:

Solaris system oracle 9i single instance database

SQL > alter system switch logfile; # hanged

Solution:

1. Generate 10046 files:

SQL > select value from v$parameter where name = 'user_dump_dest'

VALUE

/ ora/szuat/szuatdb/9.2.0/admin/SZPRD_mis-uat/udump

SQL > alter session set events' 10046 trace name context forever, level 8'

Session altered

SQL > alter system switch logfile

^ C alter system switch logfile

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SQL > alter session set events' 10046 trace name context off'

Session altered.

2. View waiting events:

WAIT # 1: nam='switch logfile command' ela= 866 p1 / 0 p2 / 0 / p3 / 0

WAIT # 1: nam='switch logfile command' ela= 439 p1 / 0 / p2 / 0 / p3 / 0

WAIT # 1: nam='switch logfile command' ela= 484 p1 / 0 / p2 / 0 / p3 / 0

WAIT # 1: nam='switch logfile command' ela= 544 p1 / 0 / p2 / 0 / p3 / 0

WAIT # 1: nam='switch logfile command' ela= 781 p1 / 0 / p2 / 0 / p3 / 0

WAIT # 1: nam='switch logfile command' ela= 576 p1 / 0 p2 / 0 / p3 / 0

WAIT # 1: nam='switch logfile command' ela= 337 p1 / 0 p2 / 0 / p3 / 0

WAIT # 1: nam='switch logfile command' ela= 383 p1 / 0 / p2 / 0 / p3 / 0

3. Modify the parameters:

SQL > show parameter log_archive_start

NAME TYPE VALUE

Log_archive_start boolean FALSE

SQL > alter system archive log start; # # alter system set log_archive_start=true scope=spfile

System altered.

4. Problem recovery:

SQL > alter system switch logfile

System altered.