"ALTER SYSTEM SWITCH LOGFILE" hangs

[gqma0]

Hello.

I'm using Oracle 9.2.0.1.0 under AIX 5.1 to replicate a database using a 3rd party tool which we develop.

I wrote scripts to put the tablespace in hotbackup mode but the postcmd script with command "alter system switch logfile" just hangs when executed the second time
from sqlplus, it works twice but hangs on the 3rd time

SQL> alter tablespace EXAMPLE begin backup
Tablespace altered
SQL> alter tablespace EXAMPLE end backup
Tablespace altered
SQL> alter database backup controlfile to trace;
Database altered
SQL> alter system switch logfile
System altered
SQL> alter tablespace EXAMPLE begin backup
Tablespace altered
SQL> alter tablespace EXAMPLE end backup
Tablespace altered
SQL> alter database backup controlfile to trace;
Database altered
SQL> alter system switch logfile
System altered
SQL> alter tablespace EXAMPLE begin backup
Tablespace altered
SQL> alter tablespace EXAMPLE end backup
Tablespace altered
SQL> alter database backup controlfile to trace;
Database altered
SQL> alter system switch logfile
HANGS AT THIS TIME UNTIL I KILL THE TASK

I uncommented LOG_ARCHIVE_START and LOG_ARCHIVE_DEST in the init.ora, bounced the instance but it doesn't make any changes
I'm not a DBA and usually get all the help from this group or from Metalink. Am I doing something wrong ??

- Also, Which SQL command shows if the tablespace is in hotbackup mode or not ??


Thanks in advance
Regards
Gaetan

 

[Scunningham99]

Post select * from v$log;
and select * from v$logfile;

Basically i would like to determine how many log groups you have as you are doing alot if log switches. Once you do a log switch the STATUS column in v$log will go from CURRENT to ACTIVE (meaning it is neaded for instance recovery). I expect if you do another log switch this will also go to active, and depending on how many groups you have it may hang because they are all active and must wait for it to become status CURRENT.

I would suggest adding more groups, if you would like to use the existing method.

But personally i would use RMAN for hot backups because rman does not create as much undo as user managed backups which you are using! As with your method, once the tablespace is put into begin backup mode the current processes begin writing to the redo logs untill it is put into end backup mode!



Sy UK

 

[gqma0]

Hi.

Sorry for the delay

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 23 104857600 1 NO INACTIVE
3297372 04-JUN-03

2 1 24 104857600 1 NO CURRENT
3297487 04-JUN-03

3 1 22 104857600 1 NO INACTIVE
3284638 04-JUN-03


SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
3 ONLINE
/oracle/dba/app/oracle/oradata/ORA920/redo03.log

2 ONLINE
/oracle/dba/app/oracle/oradata/ORA920/redo02.log

1 ONLINE
/oracle/dba/app/oracle/oradata/ORA920/redo01.log


SQL>

 

[Scunningham99]

forgot to include to check if datafile is in backup mode query

select * from v$backup; Where column "status" is equal to NOT ACTIVE or ACTIVE depending weather it is in "begin backup" or "end backup". HTH

This is strange, as your logfiles as 100m. How much activity is going on when we you are doing your hot backup?

How long does it take to complete the backup?

Have you thought about adding another group?

Ps is this a test instance, as i have noticed you only have one member per group. If test i guess ok, but if PROD, i would seriously consider multiplexing your online logs as this is a single point of failure if db cannot write to group.

to add new group 4:-

alter database add logfile '/oracle/dba/app/oracle/oradata/ORA920/redo04.log' size 100m group 4





Sy UK

 

[gqma0]

Thanks Sy.

I'll check this out and will let you know

 

[Scunningham99]

whats the crack! did it work?

Sy UK