Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
2 Pages V   1 2 >  
Reply to this topicStart new topic
> v$archived log question
BeefStu
post Apr 18 2011, 01:29 PM
Post #1


Advanced Member
***

Group: Members
Posts: 112
Joined: 6-July 10
Member No.: 43,418



Can somebody tell me what it means when I have entries in the v$archived_log table
where the column name is NULL? Is this normal?

I tried running the following to get rid of these entries but that does not work.

CROSSCHECK BACKUP DEVICE TYPE DISK;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE;



2367 748780212 1 1 2364 11755171456654 3/28/2011 8:55:26 AM 746960126 11764004041650 4/18/2011 10:00:12 AM 11764004047003 4/18/2011 10:30:12 AM 38538 512 ARCH ARCH NO YES NO YES D 4/18/2011 10:30:12 AM NO NO NO 0 1 2938200999 NO NO NO NO


2368 748782013 1 1 2365 11755171456654 3/28/2011 8:55:26 AM 746960126 11764004047003 4/18/2011 10:30:12 AM 11764004053671 4/18/2011 11:00:12 AM 40084 512 ARCH ARCH NO YES NO YES D 4/18/2011 11:00:13 AM NO NO NO 0 1 2938200999 NO NO NO NO
Go to the top of the page
 
+Quote Post
burleson
post Apr 18 2011, 01:50 PM
Post #2


Advanced Member
***

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



Hi Stu,

>> I have entries in the v$archived_log table where the column name is NULL?

Which column?

Run the script on this page please:

http://www.dba-oracle.com/t_sql_redo_log_switch_reports.htm

The v$archived_log view shows details of the progress of the managed recovery process for individual archived logs.

The following query can be used to see the status of an individual archived log on the standby site. ‘RFS’ in the REGISTRAR column shows that these logs are transferred from the primary site by log transfer services.

CODE
Select
   THREAD#,
   SEQUENCE#,
   APPLIED,
   REGISTRAR
From
   V$ARCHIVED_LOG;





--------------------
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
BeefStu
post Apr 19 2011, 08:55 AM
Post #3


Advanced Member
***

Group: Members
Posts: 112
Joined: 6-July 10
Member No.: 43,418



QUOTE (burleson @ Apr 18 2011, 02:50 PM) *
Hi Stu,

>> I have entries in the v$archived_log table where the column name is NULL?

Which column?

Run the script on this page please:

http://www.dba-oracle.com/t_sql_redo_log_switch_reports.htm

The v$archived_log view shows details of the progress of the managed recovery process for individual archived logs.

The following query can be used to see the status of an individual archived log on the standby site. ‘RFS’ in the REGISTRAR column shows that these logs are transferred from the primary site by log transfer services.

CODE
Select
   THREAD#,
   SEQUENCE#,
   APPLIED,
   REGISTRAR
From
   V$ARCHIVED_LOG;


Donald,

It's the name column in the v$archived_log table where I am seeing some columns, which have
NULL values. This is not a standby database. As for the query, here is a sample of the output.

Th# Seq# APPLIED REGISTR

---- -------- --------- -------

1 186 NO ARCH
187 NO ARCH
188 NO ARCH
....
....
....
2,452 NO ARCH
2,453 NO ARCH
2,454 NO ARCH


Note: APPLIED=NO and REGISTRAR=ARCH for all the columns in the above-mentioned query.
One more thing I would like to mention. This DB was actively cloned from my production database.
Everything appears to be working fine... I have NO problem backing up archive files via RMAN.

To give you a bit of backgroud we use the following code to backup archive files.
rman<<END
...
...
sql "alter system archive log current";
backup filesperset 5 archivelog until time 'sysdate-$KEEP_ARCHIVE_HOURS/24'
format $FILE_DEST_ARCH
delete input ;
END

The variable KEEP_ARCHIVE_HOURS is passed in the cron and can be any number (0,1,2,...)
If there are no archive logs available based on sysdate-KEEP_ARCHIVE_HOURS/24 rman
throws an error.

I want to use the query below to deteremine if there are any .arc logs to backup

select count(1) from v$archived_log
where COMPLETION_TIME < sysdate-$KEEP_ARCHIVE_HOURS/24
and backup_count = 0;

but as you can see based on the above-results of the query it will always find .arc files to backup
even though there are none on disk.

I do have a work around, by checking on disk and getting the .arc file and checking the time
but I would like to use the my query to drive the backup but I am unsure as to why I have all
these entries in the v$archived_log table.

Thanks in advance for your comments and thoughts






Go to the top of the page
 
+Quote Post
burleson
post Apr 20 2011, 08:10 AM
Post #4


Advanced Member
***

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



Hi,

>> It's the name column in the v$archived_log table where I am seeing some columns, which have
NULL values.

Sorry, I have no idea!

I would open a service request on MOSC, it may be a bug:

http://support.oracle.com

Let us know what Oracle says!


--------------------
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
SteveC
post Apr 20 2011, 08:19 AM
Post #5


Advanced Member
***

Group: Members
Posts: 2,807
Joined: 11-March 08
Member No.: 18,933



The description in the Reference Guide for this column states what null means in the NAME column.

Archived log file name. If set to NULL, either the log file was cleared before it was archived or an RMAN backup command with the "delete input" option was executed to back up archivelog all (RMAN> backup archivelog all delete input;).

http://download.oracle.com/docs/cd/E11882_...16.htm#I1030011
Go to the top of the page
 
+Quote Post
BeefStu
post Apr 20 2011, 09:26 AM
Post #6


Advanced Member
***

Group: Members
Posts: 112
Joined: 6-July 10
Member No.: 43,418



QUOTE (SteveC @ Apr 20 2011, 09:19 AM) *
The description in the Reference Guide for this column states what null means in the NAME column.

Archived log file name. If set to NULL, either the log file was cleared before it was archived or an RMAN backup command with the "delete input" option was executed to back up archivelog all (RMAN> backup archivelog all delete input;).

http://download.oracle.com/docs/cd/E11882_...16.htm#I1030011


Steve,

Thanks for pointing me in the right direction. Here is the chunk of code we use to backup our archive
logs:


sql "alter system archive log current";
backup filesperset 5 archivelog until time 'sysdate-$KEEP_ARCHIVE_HOURS/24'
format $FILE_DEST_ARCH
delete input ;
resync catalog;

As you can see it's using the the delete input command. We use a third party tool for replication and
this relies on the .arc files being present so that is the reason for the until time clause...

If I replace the delete input with "backup archivelog all delete input" would that honor my until clause
and keep the .arc files around for the specified time.

Secondly, do you know of any command, which I can use to remove these "null" name columns from
the v$archived_log file.

Donald/Steve thanks for your expertise and help!!
Go to the top of the page
 
+Quote Post
SteveC
post Apr 20 2011, 09:33 AM
Post #7


Advanced Member
***

Group: Members
Posts: 2,807
Joined: 11-March 08
Member No.: 18,933



I wouldn't do anything in terms of trying to update a data dictionary view, other than to use whatever interface (archivelog mode, RMAN, Data Guard) doing it behind the scenes. But overall, what is the issue with having null in this view? To me, it kind of falls into the "so what" category of things to worry about.
Go to the top of the page
 
+Quote Post
BeefStu
post Apr 20 2011, 11:12 AM
Post #8


Advanced Member
***

Group: Members
Posts: 112
Joined: 6-July 10
Member No.: 43,418



QUOTE (SteveC @ Apr 20 2011, 10:33 AM) *
I wouldn't do anything in terms of trying to update a data dictionary view, other than to use whatever interface (archivelog mode, RMAN, Data Guard) doing it behind the scenes. But overall, what is the issue with having null in this view? To me, it kind of falls into the "so what" category of things to worry about.


As you saw my archive code above the issue is when there are no .arc files, which meet the time
criteria RMAN balks and throws an error. I want to change the code to use the v$archive_log
table to do something like this:

select count(1) from v$archived_log where
COMPLETION_TIME < sysdate-$KEEP_ARCHIVE_HOURS/24 and backup_count = 0;

Having these entries in the the view will return "yes" there are files to archive, when there
are really none on disk to archive. I have code that checks the FS for .arc files and than compares
the time of the files against sysdate-$KEEP_ARCHIVE_HOURS/24 but this is very awkward.

That is the reason I want to see if I can clean up the view. Hope that explains it!!

FYI, I inherited this so I have to try to clean it up.

Once again, thanks for all your help
Go to the top of the page
 
+Quote Post
burleson
post Apr 20 2011, 05:30 PM
Post #9


Advanced Member
***

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



Hi Stu,

Can't you just ignore the NULL rows?

CODE
select count(1)
from v$archived_log
where
COMPLETION_TIME < sysdate-$KEEP_ARCHIVE_HOURS/24 and backup_count = 0;
and
name is not null


*******************************************
>> That is the reason I want to see if I can clean up the view. Hope that explains it!!

Yeah, that makes sense, I wish I could help you!

Can you open an SR on MOSC? This may be a bug!

Also, check the catalog.sql to see how it is built, if you want to dig into the base x$ structures . . .


--------------------
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
BeefStu
post Apr 25 2011, 12:58 PM
Post #10


Advanced Member
***

Group: Members
Posts: 112
Joined: 6-July 10
Member No.: 43,418



QUOTE (burleson @ Apr 20 2011, 06:30 PM) *
Hi Stu,

Can't you just ignore the NULL rows?

CODE
select count(1)
from v$archived_log
where
COMPLETION_TIME < sysdate-$KEEP_ARCHIVE_HOURS/24 and backup_count = 0;
and
name is not null


*******************************************
>> That is the reason I want to see if I can clean up the view. Hope that explains it!!

Yeah, that makes sense, I wish I could help you!

Can you open an SR on MOSC? This may be a bug!

Also, check the catalog.sql to see how it is built, if you want to dig into the base x$ structures . . .


Donald,

I opened and SR with Oracle regarding this issue and I am still at the very top layer of support,
meaning I am going back and forth with them trying to explain my issue to them. When I have
a definitive answer I will certainly share the knowledge....

Thanks for your help.

Go to the top of the page
 
+Quote Post
BeefStu
post May 12 2011, 02:17 PM
Post #11


Advanced Member
***

Group: Members
Posts: 112
Joined: 6-July 10
Member No.: 43,418



Donald.

Just to follow up regarding this issue the code you suggested (ignoring the nulls) appears to work
in all my cases.

select count(1)
from v$archived_log
where
COMPLETION_TIME < sysdate-$KEEP_ARCHIVE_HOURS/24 and backup_count = 0
and name is not null

What threw me is why Oracle uses backup_count and a null name to say the file has been
removed from is archive location.

Thanks for your expertise. CASE CLOSED!!


Go to the top of the page
 
+Quote Post
ManfredH
post Sep 4 2011, 08:23 AM
Post #12


Newbie
*

Group: Members
Posts: 6
Joined: 4-September 11
Member No.: 45,828



QUOTE (BeefStu @ May 12 2011, 09:17 PM) *
Donald.

Just to follow up regarding this issue the code you suggested (ignoring the nulls) appears to work
in all my cases.

select count(1)
from v$archived_log
where
COMPLETION_TIME < sysdate-$KEEP_ARCHIVE_HOURS/24 and backup_count = 0
and name is not null

What threw me is why Oracle uses backup_count and a null name to say the file has been
removed from is archive location.

Thanks for your expertise. CASE CLOSED!!


Attention!!!

I have the same issues here, just a test-db so not that big issue but...
No recovery is possible anymore.
There is a RMAN-03002 followed by a ora-06502 (number precision too large) if i try to restore (or recover) anything.
Reason is as far as I found out until now that the name column is null, and so the underlying package (X$DBMS_RCVMAN) gets this pl/sql error.

So try a restore or recovery your db before you think that problem is fixed, hopefully it's not your production environment.

Manfred

Go to the top of the page
 
+Quote Post
RHR
post Sep 7 2011, 10:54 AM
Post #13


Advanced Member
***

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



Manfred, Thank you for your valuable contribution!

For quick reference, information on RMAN v$archived_log is NULL is here:

http://dba-oracle.com/t_rman_v_archived_log_null.htm

RHR
Go to the top of the page
 
+Quote Post
ManfredH
post Sep 11 2011, 01:24 PM
Post #14


Newbie
*

Group: Members
Posts: 6
Joined: 4-September 11
Member No.: 45,828



QUOTE (ManfredH @ Sep 4 2011, 03:23 PM) *
Attention!!!

I have the same issues here, just a test-db so not that big issue but...
No recovery is possible anymore.
There is a RMAN-03002 followed by a ora-06502 (number precision too large) if i try to restore (or recover) anything.
Reason is as far as I found out until now that the name column is null, and so the underlying package (X$DBMS_RCVMAN) gets this pl/sql error.

So try a restore or recovery your db before you think that problem is fixed, hopefully it's not your production environment.

Manfred


:-(

So, there seems to be a serious problem in my control file.

.) I restore the db to a dummy db (file-copy so its a perfect duplicate)
.) still get the error when doing anything in rman (e.g. list backup, restore database....)
.) create a new controlfile
.) works again
.) catalog a backupset
.) still works
.) TRY to catalog the controlfile for the backupset (the invalid one)
.) not able to use RMAN anymore

And believe me I really tested and retested this several times, I did not make any mistakes in my conclusion here.
I also tested this with a completely new db by changing the dbid to be able to catalog the backuppieces.

Any suggestions? I really don't know what I could try now.



Go to the top of the page
 
+Quote Post
burleson
post Sep 11 2011, 01:34 PM
Post #15


Advanced Member
***

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



Hi Manfred,

Yes, this is very frustrating . . .

******************************************

>> Suggestions?

Have you checked the bugs database?

We don't have access to the Oracle souece code, your only hope is to open a service request on MOSC:

http://support.oracle.com

Let us know the solution!

I'll also ask an RMAN expert to pop in . . .



--------------------
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

2 Pages V   1 2 >
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: 19th April 2014 - 02:32 PM