OraFAQ Forum: Backup & Recovery » Urgent: Archiver Hung 10g

Home » RDBMS Server » Backup & Recovery » Urgent: Archiver Hung 10g

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

Urgent: Archiver Hung 10g [message #225843]

Wed, 21 March 2007 13:22

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #225846 is a reply to message #225843]

Wed, 21 March 2007 13:31

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

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #225919 is a reply to message #225843]

Wed, 21 March 2007 23:36

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226050 is a reply to message #225843]

Thu, 22 March 2007 07:24

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226054 is a reply to message #225846]

Thu, 22 March 2007 07:36

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226057 is a reply to message #226054]

Thu, 22 March 2007 07:40

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)

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226061 is a reply to message #225864]

Thu, 22 March 2007 07:47

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?

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226063 is a reply to message #226050]

Thu, 22 March 2007 07:50

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226065 is a reply to message #225919]

Thu, 22 March 2007 07:53

jcoxwilliams
Messages: 13
Registered: March 2007

Junior Member

How can I check the db_recovery_file_dest_size via sqlplus?

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226072 is a reply to message #226063]

Thu, 22 March 2007 08:04

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226101 is a reply to message #226072]

Thu, 22 March 2007 09:34

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.

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226107 is a reply to message #226101]

Thu, 22 March 2007 09:50

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226117 is a reply to message #226107]

Thu, 22 March 2007 10:36

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226119 is a reply to message #226117]

Thu, 22 March 2007 10:39

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226121 is a reply to message #226119]

Thu, 22 March 2007 10:44

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226132 is a reply to message #226121]

Thu, 22 March 2007 11:21

ebrian
Messages: 2794
Registered: April 2006

Senior Member

When you connect to RMAN, do you see:

connected to target database: DVCATCH

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226150 is a reply to message #226132]

Thu, 22 March 2007 12:13

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

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226153 is a reply to message #226150]

Thu, 22 March 2007 12:22

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

jcoxwilliams
Messages: 13
Registered: March 2007

Junior Member

OK. That worked. I see: connected to target database: DVCATCH <DBID=..., not open>

Thanks,
Jeanette

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226168 is a reply to message #226162]

Thu, 22 March 2007 12:58

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?

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226169 is a reply to message #225843]

Thu, 22 March 2007 13:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL > SELECT * FROM V$INSTANCE;

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226170 is a reply to message #226168]

Thu, 22 March 2007 13:03

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You're welcome.

select global_name from global_name;

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226187 is a reply to message #226169]

Thu, 22 March 2007 14:59

jcoxwilliams
Messages: 13
Registered: March 2007

Junior Member

Thanks. It worked!

Report message to a moderator

Re: Urgent: Archiver Hung 10g [message #226220 is a reply to message #226168]

Thu, 22 March 2007 21:41

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;

Report message to a moderator

Previous Topic:	scn in oracle
Next Topic:	delete archivelog files

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 15 03:32:15 CDT 2024