-
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,
-
Hi i have tried this command several times but shipped logs do not get applied to standby database.
the seq of commands i executed is as follows:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active
SQL> alter database open read only;
Database altered.
-
Oracle for sure provide some information in the alert log. Why don't you read your alert log or any trace files and find out what is going on after you start the MRM?
once again make sure that all the logs are are shipping to standby location and you don't have any archive log gaps.
Thanks,
-
man i read those files the first time i encountered the error. But not able to figure out what went wrong..MRP0 starts and completes successfully but logs do not get applied. anyways thanks for responding.
