Re: Managed recovery not working on Standby Database.

From: trub3101 <trub3101_at_sky.com>
Date: Mon, 7 Dec 2009 11:44:29 -0800 (PST)
Message-ID: <6452a5ef-52f0-4a75-b3b4-cfbf9b007d3a_at_d21g2000yqn.googlegroups.com>

On 3 Dec, 16:48, trub3101 <trub3..._at_sky.com> wrote:
> On 3 Dec, 13:54, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Dec 2, 3:45 pm, trub3101 <trub3..._at_sky.com> wrote:
>
> > > Hi all,
>
> > > Oracle Database 10g Enterprise Edition 10.2.0.3.0 Platform Windows
> > > Server 2003
>
> > > I am at a loss as to why managed recovery is not working on my standby
> > > database as expected. All the archivelogs have transported
> > > successfully across from the primary database to the standby database
> > > but no recovery occurs on the standby.
>
> > > Below are the details I have managed to gather so far. Of particular
> > > interest is the value of 'RECOVERY_MODE' which is 'IDLE' even though I
> > > started the standby correctly numerous times i.e.
>
> > > shutdown immediate;
> > > startup nomount;
> > > alter database mount standby database;
> > > alter database recover automatic managed standby database disconnect;
>
> > > Anyway, this is what I have gathered so far:
>
> > > SQL> archive log list
> > > Database log mode              Archive Mode
> > > Automatic archival             Enabled
> > > Archive destination            USE_DB_RECOVERY_FILE_DEST
> > > Oldest online log sequence     627
> > > Next log sequence to archive   0
> > > Current log sequence           629
> > > SQL> l
> > >   1  select     ds.dest_id id
> > >   2  ,  ad.status
> > >   3  ,  ds.database_mode db_mode
> > >   4  ,  ad.archiver type
> > >   5  ,  ds.recovery_mode
> > >   6  --,        ds.protection_mode
> > >   7  ,  ds.standby_logfile_count "SRLs"
> > >   8  ,  ds.standby_logfile_active active
> > >   9  ,  ds.archived_seq#
> > >  10  from       v$archive_dest_status   ds
> > >  11  ,  v$archive_dest          ad
> > >  12  where      ds.dest_id = ad.dest_id
> > >  13  and        ad.status != 'INACTIVE'
> > >  14  order by
> > >  15*    ds.dest_id
> > > SQL> /
>
> > >  ID STATUS    DB_MODE         TYPE RECOVERY_MODE    SRLs ACTIVE
> > > ARCHIVED_SEQ#
> > > --- --------- --------------- ---- ---------------- ---- ------
> > > ---------------
> > >   2 VALID     MOUNTED-STANDBY ARCH IDLE                0
> > > 0
> > > 0
> > >  10 VALID     MOUNTED-STANDBY ARCH IDLE                0
> > > 0
> > > 0
> > >  11 VALID     MOUNTED-STANDBY ARCH IDLE                0
> > > 0
> > > 628
>
> > > SQL> select * from
> > >   2  (select max(sequence#) as "Last Archived" from v$log_history),
> > >   3  (select max(sequence#) as "Last Applied" from v$archived_log
> > > where applied='YES');
>
> > >   Last         Last Applied
> > > Archived
> > > ---------------
> > > ---------------
> > >           148           148
>
> > > Thanks in advance for any suggestions,
> > > tb3101
>
> > It's not working because your alter database statement
>
> > alter database recover automatic managed standby database disconnect;
>
> > is incorrect.  This is the correct statement:
>
> > alter database recover managed standby database disconnect;
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks for your reply David,
>
> The standby database returns 'Database altered.'  for both commands.
> However, I get the 'ORA-16136: Managed Standby Recovery not active'
> error message when I cancel the recovery for either using 'alter
> database recover managed standby database cancel;' or 'alter database
> recover managed standby database cancel;'
>
> Thanks,
> tb3101- Hide quoted text -
>
> - Show quoted text -

Thanks for all your help on this one.

I deleted the archivelogs and datafiles. All initialisation parameters where already set correctly so I just backup the primary database using RMAN, copied the backup across to the standby server and then used RMAN to create the standby database switched logfile in the primary database and everything worked fine.

On the primary server:
RMAN>backup database include current controlfile for standby plus archivelog;

Copy backup across to standby server then

C:\> set ORACLE_SID=WHATEVER
C:\> sqlplus /nolog

SQL> connect / as sysdba
SQL> startup nomount
SQL> exit

Create standby database using RMAN
C:/>rman target 'sys/<password>_at_<standby connection to primary>' auxiliary /
RMAN> duplicate target database for standby nofilenamecheck dorecover;

Put the database in managed standby mode

SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover automatic managed standby database

disconnect;

After switching the logfile in the primary database everything synced up as expected.

Thanks again all who replied to my query! tb3101 Received on Mon Dec 07 2009 - 13:44:29 CST