Physical Standby Database and v$Recover_file Query
in [Oracle]
|
Prev: How to use REGEXP_SUBSTR to extract owner/obj/part from full obj name ?
Next: So whats up with the 11.2 java security hole?
From: Johne_uk on 5 Feb 2010 12:11 Hi I have created a traditional physical standby instance from a hot backup using a standby controlfile etc. from the primary db (10.2.0.4). Not using dataguard but there were no issues during creation. In standby mode if I querry v$recover file I receive the following. FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME 1 ONLINE ONLINE 8185278196312 05/02/2010 14:11:55 2 ONLINE ONLINE 8185278196312 05/02/2010 14:11:55 3 ONLINE ONLINE 8185278196312 05/02/2010 14:11:55 4 ONLINE ONLINE 8185278196312 05/02/2010 14:11:55 5 ONLINE ONLINE 8185278196312 05/02/2010 14:11:55 6 ONLINE ONLINE 8185278196312 05/02/2010 14:11:55 7 ONLINE ONLINE 8185278196312 05/02/2010 14:11:55 8 ONLINE ONLINE 8185278196312 05/02/2010 14:11:55 9 ONLINE ONLINE 8185278196312 05/02/2010 14:11:55 If I bring up the instance in read-only mode executing the same query returns nothing and there are no datafiles etc offline. Is it normal to receive this output from v$recover_file when an instance is running in standby mode. I need to have confidence that it can be activated in the event of a primary instance failure. I'm wondering if its because I used a standby controlfile and applied about 40-50 archive logs that were generated after it was created from primary. There is no archive gap either. Thanks in advance John
From: vsevolod afanassiev on 6 Feb 2010 01:38 If standby database opened in read inly mode then select from v $recoverf_file will return no rows as there is nothing to recover - all files are consistent. What exactly is 'standby mode' - managed recovery mode? Check V$DATABASE, you should gave OPEN_MODE = 'MOUNTED' DATABASE_ROLE = 'PHYSICAL STANDBY' Are you able to test activation?
From: Johne_uk on 8 Feb 2010 09:51 On 6 Feb, 06:38, vsevolod afanassiev <vsevolod.afanass...(a)gmail.com> wrote: > If standby database opened in read inly mode then select from v > $recoverf_file will return no rows as there is nothing to recover - > all files are consistent. > > What exactly is 'standby mode' - managed recovery mode? > > Check V$DATABASE, you should gave > > OPEN_MODE = 'MOUNTED' > DATABASE_ROLE = 'PHYSICAL STANDBY' > > Are you able to test activation? Hi, It is a physical standby instance running in managed receovery mode as you suggest above. I have scripts that copy the generated archive logs to the remote standby location and applied manually. i.e. alter database register physical logfile xxxxxxxx; As well as the issue above, I have just realised something else is incorrect (this is the first time I've created a standby database). The archives logs are being registered but not applied. The sql below returned all archive logs I had registed since the instance was created from primary. select sequence#, applied from v$archived_log where applied <> 'YES' I automatically purge archive logs files older than 3 days on the standby server so as they have not actually been applied then it cannot be activated (no logs to recover from). I'm aware the Dataguard looks after this process but in this scenario I cannot use Dataguard. So I obviously have missed something that will apply the logs after they have been registered. I'm wondering if I need to issue the following command periodically to apply logs:- alter database recover managed standby database cancel; alter database recover standby database until cancel; alter database recover managed standby database disconnect from session; Would this then apply all the logs that had been registered ? Many thanks
From: Johne_uk on 8 Feb 2010 10:15
On 8 Feb, 14:51, Johne_uk <edg...(a)tiscali.co.uk> wrote: > On 6 Feb, 06:38, vsevolod afanassiev <vsevolod.afanass...(a)gmail.com> > wrote: > > > If standby database opened in read inly mode then select from v > > $recoverf_file will return no rows as there is nothing to recover - > > all files are consistent. > > > What exactly is 'standby mode' - managed recovery mode? > > > Check V$DATABASE, you should gave > > > OPEN_MODE = 'MOUNTED' > > DATABASE_ROLE = 'PHYSICAL STANDBY' > > > Are you able to test activation? > > Hi, > > It is a physical standby instance running in managed receovery mode as > you suggest above. > > I have scripts that copy the generated archive logs to the remote > standby location and applied manually. > > i.e. alter database register physical logfile xxxxxxxx; > > As well as the issue above, I have just realised something else is > incorrect (this is the first time I've created a standby database). > > The archives logs are being registered but not applied. The sql below > returned all archive logs I had registed since the instance was > created from primary. > > select sequence#, applied from v$archived_log > where applied <> 'YES' > > I automatically purge archive logs files older than 3 days on the > standby server so as they have not actually been applied then it > cannot be activated (no logs to recover from). I'm aware the Dataguard > looks after this process but in this scenario I cannot use Dataguard. > > So I obviously have missed something that will apply the logs after > they have been registered. > > I'm wondering if I need to issue the following command periodically to > apply logs:- > > alter database recover managed standby database cancel; > alter database recover standby database until cancel; > alter database recover managed standby database disconnect from > session; > > Would this then apply all the logs that had been registered ? > > Many thanks FYI the last command I issued was :- alter database recover managed standby database disconnect from session; (then I start registering logs via cron jobs) cheers |