Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Difference between switch logfile and archive log current, Difference between SWITCH LOGFILE and ARCHIVE LOG ALL
cagataycebi
post Feb 21 2009, 02:25 AM
Post #1


Advanced Member
***

Group: Members
Posts: 72
Joined: 29-May 08
From: Istanbul, Turkey
Member No.: 24,688



Hi,

Can anyone tell me the differences of;

1. ALTER SYSTEM SWITCH LOGFILE;
2. ALTER SYSTEM ARCHIVE LOG ALL;
3. ALTER SYSTEM ARCHIVE LOG CURRENT;

I read Oracle's document but still didn't understand the difference in real life-use.

If you were writing a hotbackup script, which would you use at the end of datafile copy?

Thanks
Go to the top of the page
 
+Quote Post
burleson
post Feb 21 2009, 10:12 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,725
Joined: 26-January 04
Member No.: 13



ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVE LOG CURRENT both force a log switch.

From Oracle backup documentation:

QUOTE
After an online backup or inconsistent closed backup, always ensure that you have the redo necessary to recover the backup by archiving the unarchived redo logs. When the database is open, run the following SQL statement to force Oracle to switch out of the current log and archive it as well as all other unarchived logs:

ALTER SYSTEM ARCHIVE LOG CURRENT;




See example of hot baqckup script here:

http://www.dba-oracle.com/concepts/hot_backup_script.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
cagataycebi
post Feb 21 2009, 01:03 PM
Post #3


Advanced Member
***

Group: Members
Posts: 72
Joined: 29-May 08
From: Istanbul, Turkey
Member No.: 24,688



Hi Mr. Burleson

When I use "ALTER SYSTEM SWITCH LOGFILE", it makes a log switch and archives the current log. That's crystal clear. And when I try "ALTER SYSTEM ARCHIVE LOG CURRENT", I see that a log switch is happening by looking at v$logfile and v$log. And also see a new archivelog file is generated at my archivelog folder. They look like they do exact same job. This is what makes me confuse.

Is there a specific reason for choosing switch logfile after file copy?
Go to the top of the page
 
+Quote Post
burleson
post Feb 21 2009, 01:23 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,725
Joined: 26-January 04
Member No.: 13



>> Is there a specific reason for choosing switch logfile after file copy?

Yes.

It makes a "quiesce point", a firm place whereby that last redo log is a part of the hot backup, and the whole next redo is not.

Make sense?


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
cagataycebi
post Feb 21 2009, 03:00 PM
Post #5


Advanced Member
***

Group: Members
Posts: 72
Joined: 29-May 08
From: Istanbul, Turkey
Member No.: 24,688



I know we have to get current log. But I have been wondering which command (switch log or archive log current) would be suitable for a hot backup script.

I found a mail list at http://www.mail-archive.com/oracle-l@fatci...m/msg40028.html . Do you think the information is true in the mail? If that's true, I will replace 'all switch log statements' to 'archive log current' in my hot backup scripts.
Go to the top of the page
 
+Quote Post
edwardstoever
post Sep 17 2011, 01:05 PM
Post #6


Advanced Member
***

Group: Members
Posts: 555
Joined: 29-October 04
From: California
Member No.: 923



[quote name='cagataycebi' date='Feb 21 2009, 08:00 PM' post='48687']
I know we have to get current log. But I have been wondering which command (switch log or archive log current) would be suitable for a hot backup script.

If you have a single instance database, using either command will work fine. If you are on RAC, using "ALTER SYSTEM ARCHIVE LOG CURRENT" will switch the logs on all instances, whereas "ALTER SYSTEM SWITCH LOGFILE" will switch on the instance you are connected to.

Please test to verify.

Go to the top of the page
 
+Quote Post
burleson
post Sep 18 2011, 08:06 AM
Post #7


Advanced Member
***

Group: Members
Posts: 11,725
Joined: 26-January 04
Member No.: 13



Hi Edward,

Welcome back!

>> I have been wondering which command (switch log or archive log current) would be suitable for a hot backup script.

It's always safer to wait for the redo log to complete writing to the OS directory:

- “alter system switch logfile” is asynchronous: This command is fast to return to the invoking program because the writing of the redo log to the OS filesystem is done in the background. There is a very small risk in cases where the ARCH process cannot complete writing the redo log, such as cases where the OS archivelog file directory is out of space. It is also risky because the calling script may move on to a subsequent step, assuming that the redo has been written. Some scripts will place a “sleep 60” command in their backup script to allow time for the redo to complete writing, but this is not a best practice.

- “alter system archivelog current” is synchronous: This is fast because this command waits until the online redo log has completed the writing of the redo log file to the filesystem. This command is safer because it waits for the OS to acknowledge (ACK) that the redo log has been successfully written. Hence, “alter system archivelog current” is the best practice for production backup scripts with RMAN.





--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
RHR
post Sep 21 2011, 11:43 AM
Post #8


Advanced Member
***

Group: Admin
Posts: 230
Joined: 25-September 07
Member No.: 12,334



For easy reference, information on the difference betweent ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVELOG CURRENT is here:

http://dba-oracle.com/t_alter_system_switc...log_current.htm

RHR
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 27th November 2014 - 09:12 AM