Re: Determining change and sequence for incomplete recovery

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 18 Mar 2010 15:43:09 -0700
Message-ID: <bf46381003181543t1f3a5a73uf42c8fc2e5b77012_at_mail.gmail.com>

On Thu, Mar 18, 2010 at 1:19 PM, Vladimir Barac <vbarac_at_alghanim.com> wrote:

> So, when we start "recover database using backup controlfile until cancel"
> Oracle will provide suggestion:
>
> ORA-00289: suggestion: <some file name here>
>
> ORA-00280: change XYZ for thread 1 is in sequence #ABC
>

From the rman repository, which may be in the controlfile, or the rman catalog database.

controlfile:

select file_type, stamp, RL_SEQUENCE# , RL_FIRST_CHANGE#, RL_NEXT_CHANGE#, RL_NEXT_TIME
from v$backup_files
/

To find all tables/views that have both sequence# and change#, use the following query:
If you do so on an rman

select sequence#,first_change#,next_change# from v$log_history;

with systab as (

   select

      owner,
      table_name

   from dba_tab_columns
   where owner like 'SYS'
   and column_name like '%SEQUENCE#'
   intersect
   select

      owner,
      table_name

   from dba_tab_columns
   where owner like 'SYS'
   and column_name like '%CHANGE#'
)
select s.owner, s.table_name, tc.column_name from systab s
join dba_tab_columns tc on tc.owner = s.owner and tc.table_name = s.table_name
where tc.column_name like '%CHANGE#'

   or tc.column_name like '%SEQUENCE#'
order by owner, table_name, column_name

You can do a similar search in the catalog if you use one.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

>
> How does Oracle actually know that change XYZ is in particular sequence? If
> we have controlfile created from trace, where else we have this pair
> (change, sequence) stored?
>
>
>
> Regards,
>
> Vladimir Barac
>
>
>
>
>
> ______________________________________________________________________
> This e-mail message and any attachments to it are for the sole use of the
> intended recipients and may contain confidential and privileged information.
> This e-mail message and any attachments are the property of Yusuf A.
> Alghanim & Sons w.l.l. or any of its subsidiaries or affiliates (“Alghanim
> Industries”). Any unauthorized review, use, disclosure, or distribution of
> this e-mail message or its attachments is prohibited. Any opinions expressed
> in this message are those of the author and do not necessarily reflect the
> opinion of Alghanim Industries. If you are not an intended recipient, please
> notify the sender by reply e-mail and destroy all copies of the original
> message and any attachments.
> ______________________________________________________________________
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 18 2010 - 17:43:09 CDT