-
standby database query
i received the following error when i tried to open the standby database in read mode after creating it.
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\STANDBY\ORADATA\ORACL\SYSTEM01.DBF'
I repeated the steps for creating standby database thrice but got the same error everytime.
Before opening the database in read mode, i also noticed that the redo logs
have not been archived at the location of standby database specified by
log_archive_dest_2='service=stby' on primary database and standy_archive_dest on standby database.
-
then you need to make sure that your archive logs are being shipped
-
Thanks i have corrected the error. The problem was reallly with the shipping of archive logs.
Now, facing a new problem:
On my primary database, i executed the query:
select max(sequence#) from v$log_history;
output is : 20
On my standby database, i executed the query:
select max(sequence#) from v$log_history;
output is : 15
So, i started log apply services using:
recover managed standby database disconnect from session;
Again on my standby database, i executed the query:
select max(sequence#) from v$log_history;
output is : 15
Then i issued the command:
recover managed standby database cancel;
error: managed recovery not active.
So, i simply opened the database in read mode and it opened (i wondered).
It means that the logs haven't been applied as i found so because the changes on primary database did not reflect in the standby database(opened in read only mode)
-
moreover, when the database was in managed recovery mode, I issued the following command:
select process,status from v$managed_standby;
It did not show any MRPn process.
How can i overcome it?
-
Any information in the standby db alert log? any trace files are generating?
Thanks,
-
contents of standby alert log file are:
Wed Nov 19 11:40:23 2008
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 50331648
large_pool_size = 8388608
java_pool_size = 33554432
lock_name_space = stby
control_files = D:\standby\oradata\oracl\control_sb01.ctl
db_file_name_convert = e:\oracle\oradata\oracl, d:\standby\oradata\oracl
log_file_name_convert = e:\oracle\oradata\oracl, d:\standby\oradata\oracl
db_block_size = 8192
db_cache_size = 25165824
compatible = 9.2.0.0.0
remote_archive_enable = true
log_archive_start = TRUE
log_archive_dest_1 = location=d:\standby\archive mandatory reopen=300
standby_archive_dest = D:\standby\archive_primary
db_file_multiblock_read_count= 16
standby_file_management = auto
fast_start_mttr_target = 300
control_file_record_keep_time= 7
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= NONE
db_domain =
instance_name = stby
dispatchers = (PROTOCOL=TCP) (SERVICE=oraclXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = d:\standby\admin\oracl\bdump
user_dump_dest = d:\standby\admin\oracl\udump
core_dump_dest = d:\standby\admin\oracl\cdump
sort_area_size = 524288
db_name = oracl
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 25165824
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Wed Nov 19 11:40:27 2008
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=12
ARC0: Archival started
ARC1 started with pid=13
ARC1: Archival started
Wed Nov 19 11:40:28 2008
ARCH: STARTING ARCH PROCESSES COMPLETE
Wed Nov 19 11:40:28 2008
ARC0: Thread not mounted
Wed Nov 19 11:40:28 2008
ARC1: Thread not mounted
Wed Nov 19 11:40:45 2008
alter database mount standby database
Wed Nov 19 11:40:49 2008
Successful mount of redo thread 1, with mount id 1716663741.
Wed Nov 19 11:40:49 2008
Standby Database mounted.
Completed: alter database mount standby database
Wed Nov 19 11:40:52 2008
ALTER DATABASE RECOVER managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process
MRP0 started with pid=15
MRP0: Background Managed Standby Recovery process started
Starting datafile 1 recovery in thread 1 sequence 15
Datafile 1: 'D:\STANDBY\ORADATA\ORACL\SYSTEM01.DBF'
Starting datafile 2 recovery in thread 1 sequence 15
Datafile 2: 'D:\STANDBY\ORADATA\ORACL\UNDOTBS01.DBF'
Starting datafile 3 recovery in thread 1 sequence 15
Datafile 3: 'D:\STANDBY\ORADATA\ORACL\CWMLITE01.DBF'
Starting datafile 4 recovery in thread 1 sequence 15
Datafile 4: 'D:\STANDBY\ORADATA\ORACL\DRSYS01.DBF'
Starting datafile 5 recovery in thread 1 sequence 15
Datafile 5: 'D:\STANDBY\ORADATA\ORACL\EXAMPLE01.DBF'
Starting datafile 6 recovery in thread 1 sequence 15
Datafile 6: 'D:\STANDBY\ORADATA\ORACL\INDX01.DBF'
Starting datafile 7 recovery in thread 1 sequence 15
Datafile 7: 'D:\STANDBY\ORADATA\ORACL\ODM01.DBF'
Starting datafile 8 recovery in thread 1 sequence 15
Datafile 8: 'D:\STANDBY\ORADATA\ORACL\TOOLS01.DBF'
Starting datafile 9 recovery in thread 1 sequence 15
Datafile 9: 'D:\STANDBY\ORADATA\ORACL\USERS01.DBF'
Starting datafile 10 recovery in thread 1 sequence 15
Datafile 10: 'D:\STANDBY\ORADATA\ORACL\XDB01.DBF'
Media Recovery Log D:\STANDBY\ARCHIVE_PRIMARY\ARC00015.001
Incomplete recovery applied all redo ever generated.
Recovery completed through change 1023250
MRP0: Media Recovery Complete
MRP0: Background Media Recovery process shutdown
Wed Nov 19 11:40:59 2008
Completed: ALTER DATABASE RECOVER managed standby database d
Wed Nov 19 11:41:09 2008
ALTER DATABASE RECOVER managed standby database cancel
ORA-16136 signalled during: ALTER DATABASE RECOVER managed standby database c...
Wed Nov 19 11:41:30 2008
alter database open read only
Wed Nov 19 11:41:30 2008
SMON: enabling cache recovery
Wed Nov 19 11:41:31 2008
Database Characterset is WE8MSWIN1252
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open read only
Wed Nov 19 11:42:01 2008
***Warning - Executing transaction without active Undo Tablespace
Wed Nov 19 11:42:42 2008
Restarting dead background process QMN0
QMN0 started with pid=9
-
-
Please run the below commands in your standby DB and post the result.
select max(sequence#) from v$archived_log;
select max(sequence#) from v$archived_log where applied = 'YES';
Thanks,
-
on primary database:
SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
THREAD# SEQUENCE# APP
---------- ---------- ---
1 1 NO
1 2 NO
1 3 NO
1 4 NO
1 5 NO
1 6 NO
1 7 NO
1 8 NO
1 9 NO
1 10 NO
1 11 NO
THREAD# SEQUENCE# APP
---------- ---------- ---
1 12 NO
1 13 NO
1 13 NO
1 11 NO
1 10 NO
1 12 NO
1 14 NO
1 14 YES
1 15 NO
1 15 NO
1 16 NO
THREAD# SEQUENCE# APP
---------- ---------- ---
1 16 NO
1 17 NO
1 17 NO
1 18 NO
1 18 NO
1 19 NO
1 19 NO
1 20 NO
1 20 NO
1 21 NO
1 21 NO
33 rows selected.
on standby database:
SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
THREAD# SEQUENCE# APP
---------- ---------- ---
1 14 YES
1 15 NO
1 16 NO
1 17 NO
1 18 NO
1 19 NO
1 20 NO
1 21 NO
8 rows selected.
-
start redo apply and monitor the alert log on standby.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Thanks,