Re: How to read RMAN data into an Oracle Table?

From: Douglas Hawthorne <DouglasHawthorne_at_yahoo.com.au>
Date: Thu, 04 Mar 2004 10:27:47 GMT
Message-ID: <DwD1c.86656$Wa.2378@news-server.bigpond.net.au>

"W.Benvort" <wolfgang_nl_at_hotmail.com> wrote in message news:9a0caaaf.0403022215.2fbde6c4_at_posting.google.com...
> Hi,
>
> As far as my information,V$ views for RMAN are specific to Recovery
> Catalog as I read.
> If we are not using Recovery Catalog,our only source of information,
> for getting RMAN Data, are the commands like:
> RMAN>Report need backup.... etc
>
> I will appreciate your comments.
>
> W.Benvort
>
> douglashawthorne_at_yahoo.com.au (Douglas Hawthorne) wrote in message
news:<cf15dee4.0403011803.78f5ac1e_at_posting.google.com>...
> > wolfgang_nl_at_hotmail.com (W.Benvort) wrote in message
news:<9a0caaaf.0403010144.78e704fb_at_posting.google.com>...
> > > Hallo,
> > >
> > > With regards to RMAN, how should we store the data into a database
> > > table, from the commands which can run only on RMAN prompt without
> > > using Recovery Catalog?
> > >
> > > For Example:
> > >
> > > RMAN> Report need backup days 3;
> > >
> > >
> > > RMAN DATA:
> > >
> > > RMAN-03022: compiling command: report
> > > Report of files whose recovery needs more than 3 days of archived logs
> > > File Days Name
> > > ---- ----- -----------------------------------------------------
> > > 1 1203 D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF
> > > 2 1203 D:\ORACLE\ORADATA\TEST\RBS01.DBF
> > > 3 1203 D:\ORACLE\ORADATA\TEST\USERS01.DBF
> > >
> > >
> > >
> > >
> > > Should it done via UTL_FILE?
> > >
> > > If so, could someone give simple example/code how to do that, in order
> > > to write this RMAN data into an Oracle Database table?
> > > I also set the utl_file_dir parameter to a particular directory in my
> > > database.
> > >
> > > Thanks
> > >
> > > W.Benvort
> >
> > Wolfgang,
> >
> > Since you are not using the RMAN catalog (or repository), the source
> > of the data is the control file. This data is already available in
> > the dynamic performance views over the control file. Please consult
> > the Database Reference manual to find what views you want.
> >
> > Douglas Hawthorne

Wolfgang,

The V$ views, such as V$BACKUP_DATAFILE, are over the control files for the target database. That is, when you execute rman TARGET /@target_db NOCATALOG, the source of the RMAN data is the control files for the database identified by TNSNAMES.ORA as target_db.

Please refer to "Storage of the RMAN Repository Exclusively in the Control File" on p.4-14 of "Oracle9 i Recovery Manager User's Guide".

Take the REPORT NEED BACKUP; command. The data used comes from the V$BACKUP_DATAFILE, V$RMAN_CONFIGURATION, and V$DATAFILE views on the target database. V$RMAN_CONFIGURATION would be interrogated to find what backup retention policy is use.

SELECT value FROM v$rman_configuration WHERE name = 'RETENTION POLICY';

The value returned could be TO REDUNDANCY 2 If so, a query similar in intent to the following would be executed to give the same output as the RMAN command.

SELECT

      b.file#,
      b.num_bkps,
      d.name
   FROM
      (SELECT
            file#,
           COUNT(*) num_bkps
         FROM
              v$backup_datafile
        GROUP BY file#
        HAVING COUNT(*) < 2 -- Redundancy criterion
       ) b,
     v$datafile d

    WHERE b.file# = d.file#
;

Douglas Hawthorne Received on Thu Mar 04 2004 - 04:27:47 CST