Hello all,
We have created a standby.How to check whether it is working or not.
can I use the command alter database open read only and connect as user
and check all rows are present in standby as well as in primary database.
Printable View
Hello all,
We have created a standby.How to check whether it is working or not.
can I use the command alter database open read only and connect as user
and check all rows are present in standby as well as in primary database.
hi,
If standby can be opened in read only mode.Here is command i have given and the error it showed.
sql>alter database open read only;
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/mnt/db/oracle/oradata/ISPRO20/system01.dbf'
you havent create dyour standby correctly or you havent applied all the necessary archive logs
It looks like datafile '/mnt/db/oracle/oradata/ISPRO20/system01.dbf' is younger than the other files of your database, in plain english, this file is "in the future".
I have taken fresh hot backup from production(.dbf,redolog files to standby) created controlfile and performed performed recoery..etc.(the same task what i had done before).
It shows..
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/db/oracle/oradata/ISPRO20/system01.dbf'
this time it shows different error.then I performed following task..
SQL> alter database recover standby datafile 1 until consistent with controlfile;
alter database recover standby datafile 1 until consistent with controlfile
*
ERROR at line 1:
ORA-00279: change 942879428 generated at 03/28/2007 00:02:10 needed for thread
1
ORA-00289: suggestion : /mnt/backup/arch/ISPRO20/-1063755125_1_14501.arc
ORA-00280: change 942879428 for thread 1 is in sequence #14501
It shows sequence #14501.But we have that sequence #14501 in that path.
So pls help how to proceed further ...
Use the following in the mount stage of your standby database:
SQL> recover standby database;
write "auto" when it asks for archive log files. (Assuming you check the archives transferred from primary to standby locations)
SQL> alter database open read only;
Post the output.
hi paresh..
I had simulated the same error in test box and succeeded in that,but frustation was what went wrong i can't find.
What i did was..
switched log files on prod, and then worked in standby which repeats the same error.I shut down both dband went out came back open standby alone and made usual recovery that i had done previously.
SQL>alter database recover managed standby database disconnect from session;
SQL>recover managed standby database cancel;
SQL>alter database open read only;
It holds for a while and then opened in read only mode(What a wonder..?)
then i shutdowned standby;
Then went to prod kept in mount state,then standby kept in mount,then opened prod and it SYNC with standby.
!!!!Working properly.
I reported to my boss,and asked for down time for real PRODDB.He too accepted next weekend.
I have made up my mind to do the same process.
I will send you next week the result.
If u have any idea on this regard kindly inform me.What actually went wrong.
Quote:
Originally Posted by ams-jamali
You must not have applied the last archive log shipped.
No.,I have checked latest archive that is generated in prod and checked in standby too,the archive log number is same in both.
(Another thing i made is
alter system switch log file in prod and checked in standby whether it is sync with prod. )
Quote:
Originally Posted by ams-jamali
How did you check?
Please provide output of the following query:
On Primary:
SQL> select max(sequence#) from v$log_history;
On Standby:
SQL> select max(sequence#) from v$log_history;
Should be same.
I have not checked like that.
select * from v$log;
in prod & standby
it will you the current status (i.e.,)current,Active,Inactive.
If i do Switch log file the current status changes to next log.
check that using select * from v$log;
in both DB.
from that i confirmed.
then added 5 records in test user in prod. after that went to standby checked it shows only previos record, then shutdowned standby.
recoverd..,Opened in read only then checked
Newly added five records displayed in standby.
Quote:
Originally Posted by ams-jamali
Please provide output of the following query:
On Primary:
SQL> select max(sequence#) from v$log_history;
On Standby:
SQL> select max(sequence#) from v$log_history;
Should be same.
Hi parvesh,
we have worked successfully in standby database.
we opened standby in readonly mode.everything is perfect,but there is one thing controversy.Undo tablespace used in prod is UNDOTBS whereas in standby it is UNDOTBS1.
when we kept standby in read only mode .it shows warning in alert_log file stating that
***Warning - Executing transaction without active Undo Tablespace
everything is perfect,but little bit scared whether it will show err in future.
Thanks to all
*bore,parvesh,ray_dp,dave..& all