Home » RDBMS Server » Server Administration » ARCHIVAL Growth = 9Gb a day..
ARCHIVAL Growth = 9Gb a day.. [message #249944] Fri, 06 July 2007 10:23 Go to next message
Koolwant
Messages: 49
Registered: June 2007
Location: new jersey
Member
Hey gurus

I have 10.2.0.1 oracle on my server.
My database size is about 2 Gb.
The archival growth is around 9 Gb a day.So I am having problem in managing space. Temporarily I am deleting old archive files ( Cron Job.) I am little concerned about what should i do thinking long term?. Should i put my database in Noarchivelog or ??
Also the error i saw in alert log is

Fri Jul 6 10:53:52 2007
Thread 1 cannot allocate new log, sequence 4119
Checkpoint not complete
Current log# 1 seq# 4118 mem# 0: /home/oracle/product/10.2.0/db_1/oradata/VMXLA1/redo01.log
Thread 1 advanced to log sequence 4119
Current log# 2 seq# 4119 mem# 0: /home/oracle/product/10.2.0/db_1/oradata/VMXLA1/redo02.log
Thread 1 cannot allocate new log, sequence 4120
Checkpoint not complete
Current log# 2 seq# 4119 mem# 0: /home/oracle/product/10.2.0/db_1/oradata/VMXLA1/redo02.log
Thread 1 advanced to log sequence 4120
Current log# 3 seq# 4120 mem# 0: /home/oracle/product/10.2.0/db_1/oradata/VMXLA1/redo03.log

Any suggestions from anybody ? how to deal with it..??

Regards

Re: ARCHIVAL Growth = 9Gb a day.. [message #249946 is a reply to message #249944] Fri, 06 July 2007 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You may have to use more and/or bigger redo logs.
You may have to add new database writers.
It depends on wait events.
To know wait events use StatsPack.

If all this does say nothing to you search for all these words (the last ones of each sentence) in Oracle documentation.

Regards
Michel

[Updated on: Fri, 06 July 2007 10:28]

Report message to a moderator

Re: ARCHIVAL Growth = 9Gb a day.. [message #249951 is a reply to message #249944] Fri, 06 July 2007 10:36 Go to previous messageGo to next message
Koolwant
Messages: 49
Registered: June 2007
Location: new jersey
Member
Thanks for Reply Michel

The size of redo logs is 50 MB , three of them.

How can i add database writers.

Does the wait events realte with Archival Growth?

Regards


Re: ARCHIVAL Growth = 9Gb a day.. [message #249955 is a reply to message #249946] Fri, 06 July 2007 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does the wait events realte with Archival Growth?

Thread 1 cannot allocate new log, sequence 4119
Checkpoint not complete

There a process is waiting for a redo log.

See how many time between each switch (v$log_history), calibrate your logs to have 4 to 10 per hour.

Quote:
How can i add database writers.

db_writer_processes parameter.

I didn't say you have to do all that.
I said depending on the situation you have to do one, several or all of that.
Maybe you have to activate dbwr io slaves
Maybe you have to do other things.

But you surely have to investigate -> StatsPack, iostat, vmstat...

Regards
Michel

[Updated on: Fri, 06 July 2007 10:45]

Report message to a moderator

Re: ARCHIVAL Growth = 9Gb a day.. [message #249959 is a reply to message #249955] Fri, 06 July 2007 10:47 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
Should i put my database in Noarchivelog or ??


Thats an option if you can tolerate transaction loss..
Re: ARCHIVAL Growth = 9Gb a day.. [message #249961 is a reply to message #249959] Fri, 06 July 2007 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Thats an option if you can tolerate transaction loss...

I even don't talk about that option which scares me for an OLTP production database.
If he does that I bet we'll soon have another question from him asking how to recover his data when his datatabase is in NOARCHIVELOG mode.

Regards
Michel
Re: ARCHIVAL Growth = 9Gb a day.. [message #249962 is a reply to message #249961] Fri, 06 July 2007 11:04 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
haha Laughing Laughing
Re: ARCHIVAL Growth = 9Gb a day.. [message #249968 is a reply to message #249961] Fri, 06 July 2007 11:26 Go to previous messageGo to next message
Koolwant
Messages: 49
Registered: June 2007
Location: new jersey
Member
Thanks Michel and Dreamzz for your replies.

I Know and i dont even want to put my database in archive log

The problem is i dont have much disk space with me . so i have to Delete old archive log files time to time.

Thanks for the suggestion to add more logfiles.
I will definitely check StatsPack, iostat, vmstat

So in a nutshell what shoul i do ? should i keep deleting old log files. or there can be any other way out..

Regards

Re: ARCHIVAL Growth = 9Gb a day.. [message #249969 is a reply to message #249968] Fri, 06 July 2007 11:33 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
yes delete old archivelogs after taking RMAN backup.

and use compressed CLAUSE in backup command.It will also save your disk space.
like

RMAN>backup as compressed backupset database;

example
 ACTUALL SIZE            BACKUPSIZE BY COMPRESSED OPTION
 38.50G	                          13.54G

                         BACKUPSIZE By UN COMPRESSED OPTION
 28.58G                            28.01G

[Updated on: Fri, 06 July 2007 11:34]

Report message to a moderator

Re: ARCHIVAL Growth = 9Gb a day.. [message #249973 is a reply to message #249969] Fri, 06 July 2007 11:47 Go to previous messageGo to next message
Koolwant
Messages: 49
Registered: June 2007
Location: new jersey
Member
Thanks Dreamzz

Have a nice weekend.

Regards

Re: ARCHIVAL Growth = 9Gb a day.. [message #249975 is a reply to message #249973] Fri, 06 July 2007 11:53 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Yw and You to Wink
Re: ARCHIVAL Growth = 9Gb a day.. [message #249979 is a reply to message #249973] Fri, 06 July 2007 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey, don't forget to also backup your database.
Archived logs are useless if you don't have a backup of the database.
Moreover, if you don't want more then one backup, you can delete all archived logs generated before the last database backup.

Regards
Michel
Re: ARCHIVAL Growth = 9Gb a day.. [message #249983 is a reply to message #249979] Fri, 06 July 2007 12:24 Go to previous message
Koolwant
Messages: 49
Registered: June 2007
Location: new jersey
Member
Thanks Michel

Regards
Previous Topic: consolidation
Next Topic: fragmentation
Goto Forum:
  


Current Time: Fri Dec 09 05:35:12 CST 2016

Total time taken to generate the page: 0.19577 seconds