OraFAQ Forum: Backup & Recovery » change archive log mode during DB running

Home » RDBMS Server » Backup & Recovery » change archive log mode during DB running (all 10g/11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

change archive log mode during DB running [message #520753]

Wed, 24 August 2011 09:42

edgefree
Messages: 30
Registered: July 2010
Location: Toronto

Member

Hi Gurus,

I have a question about changing archive mode during DB running.

Before in 9.2, 'ALTER SYSTEM ARCHIVE LOG START/STOP' is available for this job. But, after 10g/11g, it cause 'archive log stop has been deprecated'.

However, for my practice, I need to estimate archived logs size before backup them, so I need to stop archive log for a while and enable it later during DB running.

I check Oracle documents, it only mentions changing archive log mode at DB mounted but not open. This is not the moment I need.

Do you have any suggestion?

Thank you all in advance!

Edgy

Report message to a moderator

Re: change archive log mode during DB running [message #520755 is a reply to message #520753]

Wed, 24 August 2011 10:01

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>This is not the moment I need

You mean, ability to change with open database?
Not possible. Should it be , it would be disastrous.

If this is what you actually looking into
>>I need to estimate archived logs size
you have that information in v$log_history.

[Updated on: Wed, 24 August 2011 10:14]

Report message to a moderator

Re: change archive log mode during DB running [message #520757 is a reply to message #520753]

Wed, 24 August 2011 10:11

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I have a question about changing archive mode during DB running.

Before in 9.2, 'ALTER SYSTEM ARCHIVE LOG START/STOP' is available for this job

This does not change the archive log mode, this starts/stops the archiver.

Quote:

for my practice, I need to estimate archived logs size before backup themso I need to stop archive log for a while and enable it later during DB running.

Just query v$archived_log.

Regards
Michel

Report message to a moderator

Re: change archive log mode during DB running [message #520760 is a reply to message #520755]

Wed, 24 August 2011 10:17

edgefree
Messages: 30
Registered: July 2010
Location: Toronto

Member

Mahesh Rajendran wrote on Wed, 24 August 2011 10:01

>>This is not the moment I need

You mean, ability to stop ability with open database?
Not possible. Should it be , it would be disastrous.

Yes. Little bit correction, pause to automatic archiving is what I want.

Mahesh Rajendran wrote on Wed, 24 August 2011 10:01

If this is what you actually looking into
>>I need to estimate archived logs size
you have that information in v$log_history.

If not stopping/pausing automatic archiving, I am afraid of the incorrect size since the size is keep changing.

Am I right?

Report message to a moderator

Re: change archive log mode during DB running [message #520761 is a reply to message #520760]

Wed, 24 August 2011 10:27

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The size of each archived log is not changing.
The global size may but it is life or make a cold backup.
Do you realize that the backup itself generates log records?

Regards
Michel

Report message to a moderator

Re: change archive log mode during DB running [message #520763 is a reply to message #520760]

Wed, 24 August 2011 10:28

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> I am afraid of the incorrect size
Well,
that is the difference between estimating and calculating.
The archive log size depends on many things.
You can estimate it with v$log_history/v$archived_log (based on number of switches, historically).

[Updated on: Wed, 24 August 2011 10:29]

Report message to a moderator

Re: change archive log mode during DB running [message #520766 is a reply to message #520761]

Wed, 24 August 2011 10:58

edgefree
Messages: 30
Registered: July 2010
Location: Toronto

Member

Michel Cadot wrote on Wed, 24 August 2011 10:27

The size of each archived log is not changing.
The global size may but it is life or make a cold backup.
Do you realize that the backup itself generates log records?

Regards
Michel

Good point! You maybe right. I have not realize backup itself generates log records. I thought backup only write controlfile for metadata before. Are you sure archived log also record backup information? Thank you for guiding.

Report message to a moderator

Re: change archive log mode during DB running [message #520790 is a reply to message #520766]

Wed, 24 August 2011 12:48

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I can't show you but take a test database, no activity, take the redo generated from v$sysstat, make a backup, take the redo generated after the end of the backup and you will see the statistics has increased (not only from backup but other background processes).

select statistic# nb, name, value from v$sysstat where name like '%redo%';

So, you need to let the archiver work otherwise you may block the database and your backup (and this is why this statement was removed).

Regards
Michel

Report message to a moderator

Re: change archive log mode during DB running [message #520793 is a reply to message #520790]

Wed, 24 August 2011 13:02

edgefree
Messages: 30
Registered: July 2010
Location: Toronto

Member

Thank you very much. I also did some testing. After making any backup, even if current controlfile, DB does make redo archived (maybe in my testing environment, my redo log size is small for testing).

Here another issue is coming. One of our senior developer told me, in Oracle 9i, if not disable archiver during backing up, it could cause unexpected errors (which one he didn't mention). During my testing, I found sometime my 9.2 on SLES9 did run into ora-0600 internal error from time to time, but I am not 100% sure that it is related to 'alter system archive log stop/start' command. Any comments?

Report message to a moderator

Re: change archive log mode during DB running [message #520799 is a reply to message #520793]

Wed, 24 August 2011 13:54

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

if not disable archiver during backing up, it could cause unexpected errors (which one he didn't mention).

I didn't encounter any in hundred of thousand backups, if your senior deveoper can tell us which one...

Quote:

During my testing, I found sometime my 9.2 on SLES9 did run into ora-0600 internal error from time to time

Without more information, you can't say if it comes from backup or anything, and even without patchset number you can't say if it has been fixed in a further patchset. Every version has many and many ORA-600, this is a common property of software to have bugs.

Regards
Michel

[Updated on: Wed, 24 August 2011 13:55]

Report message to a moderator

Re: change archive log mode during DB running [message #520800 is a reply to message #520799]

Wed, 24 August 2011 14:21

edgefree
Messages: 30
Registered: July 2010
Location: Toronto

Member

Thank Michel for guiding.

In 10g/11g, I force run 'alter system archive log stop' in sqlplus cmd when DB running as archive log mode, no any errors are shown on sqlplus. However, I monitor alert_log, I found:
" Shutting down archive processes
ARCH shutting down
ARC2: Archival stopped
ARCHIVE LOG STOP has been deprecated - see ALTER DATABASE ARCHIVELOG"

However, when I check "select archiver from v$instance", I still get 'Started' result.

I am wondering if 'alter system archive log stop' cmd stops archiver process in fact? If not, why alert_log shows weird information?

Thank you all again.

Report message to a moderator

Re: change archive log mode during DB running [message #520801 is a reply to message #520800]

Wed, 24 August 2011 14:31

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ The command does nothing
2/ The alert log tells you that it did nothing. Maybe the message is not really appropriate, you can ask Oracle to change it.

The documentation states (for ALTER SYSTEM ARCHIVE LOG STOP):
Quote:

STOP Clause

In earlier releases, this clause disabled automatic archiving of redo log file groups for the thread assigned to your instance. This clause has been deprecated. It has no effect, and if you specify it, Oracle Database writes a message to the alert log.

Regards
Michel

Report message to a moderator

Re: change archive log mode during DB running [message #520810 is a reply to message #520801]

Wed, 24 August 2011 16:18

edgefree
Messages: 30
Registered: July 2010
Location: Toronto

Member

Yes, I read these today too. Just confused by alert_xxxx.log.

I have some thoughts here to share. Currently, 10g/11g sets log_archive_max_processes=2 by default. So, in runningDB, supposed at most there are 2 ARCx running. What I guess is: when there are two or more ARCx running, execute 'archive log stop' will stop ARCx until keeping one ARCx running. So, it may be a reason I found 'ARC2 shutting down' in alert_log. Because we cannot disable archiver, even if execute 'archive log stop', "it has no effect", as Oracle document says.

I may make mistakes. Point it out if you don't agree. Smile

Report message to a moderator

Previous Topic:	Restore Oracle database
Next Topic:	New backup on networkdrive - cant read from new host

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 15:35:04 CST 2024