Home » RDBMS Server » Backup & Recovery » Urgent: Archiver Hung 10g
Urgent: Archiver Hung 10g [message #225843] Wed, 21 March 2007 13:22 Go to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
Hello,

I am new to Oracle and am using Oracle 10g. I cannot access my database. It looks like the archiver ran out of disk space. I deleted old archives directly from the file system. I have shutdown and restart the database. I have rebooted the server, and I still cannot access my database via Enterprise Manager. Initially, the error I received was ORA-00257. The next error I received was saying I could not connect. Now when EM comes up it is red and gives the option to startup. When I press that it says connection refused.

How do verify that the oracle_sid I think I am using?

How do I fix the archive issue?

Is there a way to log into the database to reset everything?

Thanks,
Jeanette
Re: Urgent: Archiver Hung 10g [message #225846 is a reply to message #225843] Wed, 21 March 2007 13:31 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Welcome to the wonderful world of an Oracle DBA.

I assume you are using the Flash Recovery Area (FRA)?

If so, you should do the following:

C:\> set oracle_sid=<your_sid_name>
C:\> rman target /

RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

[Updated on: Wed, 21 March 2007 13:50]

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #225864 is a reply to message #225846] Wed, 21 March 2007 15:25 Go to previous messageGo to next message
salaku
Messages: 9
Registered: March 2007
Junior Member
Also after deleting the files from the filesystem and using rman
did u issue

SQL> alter system archive log all;

issue

select * from v$log

and see if ARCHIVED column is yes for all other than the current redolog


Re: Urgent: Archiver Hung 10g [message #225919 is a reply to message #225843] Wed, 21 March 2007 23:36 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
or what is your db_recovery_file_dest_size value >>> ?

if low you should increase

regards
Taj
Re: Urgent: Archiver Hung 10g [message #226050 is a reply to message #225843] Thu, 22 March 2007 07:24 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
When I first created the database it was in archivelog mode. It is no longer in archivelog mode. I checked the mode by issuing this command SQL>select log_mode from v$database;. The result is NoArchiveLog.

I logged into RMAN and issued the crosscheck command, but it did not work.

I set the oracle_sid before logging into sqlplus. Should I be able to see some of the actual tables in my database by issueing the command SQL>select * from schema.tablename? Regardless of what table name I issue, it says table or view does not exist.

Thanks,
Jeanette
Re: Urgent: Archiver Hung 10g [message #226054 is a reply to message #225846] Thu, 22 March 2007 07:36 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
I issued the command C:\> rman target /
then RMAN> CROSSCHECK ARCHIVELOG ALL;
The result was: using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1 channel ORA_DISK1: sid = 10 devtype=Disk
specification does not match any archive log in the recovery catalog

issed: RMAN>DELETE EXPIRED ARCHIVELOG ALL;
result: released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid = 10 devtype = DISK
specification does not match any archive log in the recovery catalog



Re: Urgent: Archiver Hung 10g [message #226057 is a reply to message #226054] Thu, 22 March 2007 07:40 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
Following is some if the information from the alert log:

Errors in file c:\oracle\product\10.2.0\admin\dvcatch\bdump\dvcatch_arc1_3080.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 16106127360 bytes is 100.00% used, and has 0 remaining bytes available.

Wed Mar 21 12:44:02 2007
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARC1: Failed to archive thread 1 sequence 1459 (19809)
ARCH: Archival stopped, error occurred. Will continue retrying
Wed Mar 21 12:44:02 2007
Errors in file c:\oracle\product\10.2.0\admin\dvcatch\bdump\dvcatch_arc1_3080.trc:
ORA-16038: log 3 sequence# 1459 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'D:\ORACLE\DATA\DVCATCH\REDO03.LOG'

Wed Mar 21 12:44:04 2007
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 1459 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Wed Mar 21 12:44:04 2007
Errors in file c:\oracle\product\10.2.0\admin\dvcatch\bdump\dvcatch_arc0_2480.trc:
ORA-16014: log 3 sequence# 1459 not archived, no available destinations
ORA-00312: online log 3 thread 1: 'D:\ORACLE\DATA\DVCATCH\REDO03.LOG'

Wed Mar 21 12:45:04 2007
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1459 (4)
Wed Mar 21 12:46:04 2007
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 1459 (4)
Wed Mar 21 12:47:04 2007
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1459 (4)
Re: Urgent: Archiver Hung 10g [message #226061 is a reply to message #225864] Thu, 22 March 2007 07:47 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
After issueing: SQL> alter system archive log all;
result: ORA-00258 manual archiving in NOARCHIVELOG mode must identify log

Issued: SQL>select * from v$log
result: in the archive column no is listed for all redo logs including the current.

Is there a way to put this database in archivelog mode again through sqlplus? Will this help? Or should I just try to restore from backup at this point?
Re: Urgent: Archiver Hung 10g [message #226063 is a reply to message #226050] Thu, 22 March 2007 07:50 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
You must increase your db_recovery_file_dest_size parameter values.
and please just show below sql query output.
sql>archive log list


select * from v$recovery_file_dest;



regards
Taj
Re: Urgent: Archiver Hung 10g [message #226065 is a reply to message #225919] Thu, 22 March 2007 07:53 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
How can I check the db_recovery_file_dest_size via sqlplus?
Re: Urgent: Archiver Hung 10g [message #226072 is a reply to message #226063] Thu, 22 March 2007 08:04 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
SQL> archive log list
result:Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 320
Current log sequence 322



SQL>select * from v$recovery_file_dest;
result:SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FIlES
c:\oracle\product\10.2.0/flash_recovery_area
2147483648 0 0 0
Re: Urgent: Archiver Hung 10g [message #226101 is a reply to message #226072] Thu, 22 March 2007 09:34 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
jcoxwilliams wrote on Thu, 22 March 2007 09:04
SQL>
SQL>select * from v$recovery_file_dest;
result:SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FIlES
c:\oracle\product\10.2.0/flash_recovery_area
2147483648 0 0 0

Your query seems to contradict what your alert log indicates. How many databases do you have on this box?

Increasing your db_recovery_file_dest_size parameter isn't going to solve your problem, it's only going to delay it from happening again.
Re: Urgent: Archiver Hung 10g [message #226107 is a reply to message #226101] Thu, 22 March 2007 09:50 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Increasing your db_recovery_file_dest_size parameter isn't going to solve your problem, it's only going to delay it from happening again.

Yes, correct,
but atleast db_recovery_file_dest_size have sufficient value.



regards
Taj
Re: Urgent: Archiver Hung 10g [message #226117 is a reply to message #226107] Thu, 22 March 2007 10:36 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
There are 3 databases on this box.

I increased the db_recovery_dest_size to 10GB, but still no luck.

Thanks,
Jeanette
Re: Urgent: Archiver Hung 10g [message #226119 is a reply to message #226117] Thu, 22 March 2007 10:39 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Actually you mix everything..
you have three database on some box

1. On which database your getting error.
2. On which database your increase db_recovery_file_dest_size.
3. On which database you issue >CROSSCHECK ARCHIVELOG ALL<
                               >DELETE EXPRIED ARCHIVELOG ALL<


REGARDS
tAJ
Re: Urgent: Archiver Hung 10g [message #226121 is a reply to message #226119] Thu, 22 March 2007 10:44 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
I was working from the command prompt since I could not use Oracle Enterprise Manager. I set the oracle_sid to dvCatch which is the database that has the problem. I logged into RMAN specifying the target as dvCATCH and then ran the crosscheck command.

Thanks,
Jeanette
Re: Urgent: Archiver Hung 10g [message #226132 is a reply to message #226121] Thu, 22 March 2007 11:21 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
When you connect to RMAN, do you see:

connected to target database: DVCATCH
Re: Urgent: Archiver Hung 10g [message #226150 is a reply to message #226132] Thu, 22 March 2007 12:13 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
No. I see connected to ORCL <DBID=...

How can I force it to connect to the right database?

When I set the oracle sid I enter:
c:\>set oracle_sid = dvcatch

When I log into RMAN, I enter:
c:\>RMAN target= dvcatch

Thanks,
Jeanette
Re: Urgent: Archiver Hung 10g [message #226153 is a reply to message #226150] Thu, 22 March 2007 12:22 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Lose the space around the equal sign (=) in the set command:

C:\> set oracle_sid=dvcatch
C:\> rman target /

[Updated on: Thu, 22 March 2007 13:00]

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226162 is a reply to message #226150] Thu, 22 March 2007 12:40 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
OK. That worked. I see: connected to target database: DVCATCH <DBID=..., not open>

Thanks,
Jeanette
Re: Urgent: Archiver Hung 10g [message #226168 is a reply to message #226162] Thu, 22 March 2007 12:58 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
THANK YOU! THANK YOU! THANK YOU!

I logged into RMAN and ran the crosscheck command and then the delete all expired command. From there I restarted the database service and the dbconsole service, and then tried to log into my database and it worked!

Is there a way to tell that you are connected to the right database from sqlplus command line utility? Should I also see 'connected to' and the name of the database I am expecting?
Re: Urgent: Archiver Hung 10g [message #226169 is a reply to message #225843] Thu, 22 March 2007 13:02 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SQL > SELECT * FROM V$INSTANCE;
Re: Urgent: Archiver Hung 10g [message #226170 is a reply to message #226168] Thu, 22 March 2007 13:03 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You're welcome.

select global_name from global_name;
Re: Urgent: Archiver Hung 10g [message #226187 is a reply to message #226169] Thu, 22 March 2007 14:59 Go to previous messageGo to next message
jcoxwilliams
Messages: 13
Registered: March 2007
Junior Member
Thanks. It worked!
Re: Urgent: Archiver Hung 10g [message #226220 is a reply to message #226168] Thu, 22 March 2007 21:41 Go to previous message
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
Member
Is there a way to tell that you are connected to the right database from sqlplus command line utility?


sql>select name from v$database;
Previous Topic: scn in oracle
Next Topic: delete archivelog files
Goto Forum:
  


Current Time: Sat Dec 03 12:08:49 CST 2016

Total time taken to generate the page: 0.14136 seconds