| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> "Alter system switch logfile" hangs
Morning,
when you do hot backups, you need to be in archivelog mode.
You have turned ON the ARCH process by setting the log_archive_start and log_archive_dest but you still need to turn on archivlog mode which you would do as follows :
shutdown.
startup mount
alter database archivelog
shutdown
Set (as you have done) the log_archive_start and log_archive_dest
params.
cold backup
startup.
check with the command archive log list to make sure you are running as required.
Now, when you put a tablespace into backup mode, any change to that tablespace will cause the complete block (say 8Kb) to be written to the log buffer and hence the redo log files. This means that you should put ONE tablespace into backup, secure it using an OS routine then take it out of backup then do the next tablespace and so on.
If the database is hanging then it should show something in the alert log which you can find in the BACKGROUND_DUMP_DEST init.ora parameter. Look in the aleert file at the end for any messages. If you see something along the lines of 'cannot archive blah' then your database is in archivelog mode but ARCH has not yet been able to archive off the on-line redolog files, so, to prevent any loss of data, the system stop work until you fix the problem that is causing ARCH to pause. The problem is usually not enough disc space in LOG_ARCHIVE_DEST to take any more copies of the log files.
An online redolog file is archived on a logswitch. If the logfile being switched INTO is archived already, then it can start to be used, however, if it has not yet completed the archiving, or ARCH has hung, then the switch will not be made until the file has been safely archived. This *could* be your problem.
The command to check a tablespace is :
set lines 132
column ts_name format a30
column file_name format a30
column as_of format a16
select v$tablespace.name as TS_NAME, v$datafile.name as FILE_NAME, to_char(v$backup.time,'DD/MM/YYYY HH:MI') as AS_OF from v$tablespace, v$datafile, v$backup where v$tablespace.ts# = v$datafile.ts# and v$datafile.file# = v$backup.file# and v$backup.status = 'ACTIVE' order by v$tablespace.name, v$datafile.name;
This will show the files in each tablespace which are active - in backup mode.
Having said all that, you can put a single datafile into backup mode using :
alter database datafile 'filename' begin backup
and this only causes lots of redo for the file in question - especially useful if there are lots of files in the tablespace.
HTH
Regards,
Norman.
PS. READ and UNDERSTAND the Oracle Backup & Recovery manaul !!!
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: gaetan_at_rocketmail.com (Gaetan) [mailto:gaetan_at_rocketmail.com]
Posted At: Wednesday, June 04, 2003 11:07 AM
Posted To: server
Conversation: "Alter system switch logfile" hangs
Subject: "Alter system switch logfile" hangs
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'm not a DBA and usually get all the help from this group or from Metalink but I'm running out of ideas and time.
I wrote scripts to put the tablespace in hotbackup mode but the command "alter system switch logfile" just hangs when executed the second time.
When I run the query manually, the command hangs after 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
HANGS AT THIS TIME UNTIL I PRESS CTRL^C
No .ARC files are being created. 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.
Thanks in advance
Regards
Gaetan
Received on Wed Jun 04 2003 - 06:13:17 CDT
 |
 |