dataguard 出现ORA-16136错误

显示全部楼层 · 2011-6-30 12:30:01

做好了dataguard，每次切换归档日志，在主备库查询如下结果都一致
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------

1543
说明至少是接收到了日志

备库上查询日志应用情况
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP
---------- ---
1461 YES
1462 YES
1463 YES
1464 YES
1465 NO
1466 NO
1467 NO
1468 NO
1469 NO
1470 NO
1471 NO
SEQUENCE# APP
---------- ---
1472 NO
1473 NO
1474 NO
1475 NO
1476 NO
1519 YES
1520 YES
1521 YES
1522 YES
1523 NO
1523 NO
SEQUENCE# APP
---------- ---
1524 NO
1525 NO
1526 NO
1527 NO
1528 NO
1529 NO
1530 NO
1531 NO
1532 NO
1533 NO
1534 NO
SEQUENCE# APP
---------- ---
1535 NO
1536 NO
1537 NO
1538 NO
1539 NO
1540 NO
1541 NO
1542 NO
1543 NO
有这么多没有应用
以为是没有启动实时应用，于是
在备库启动实时应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
也是成功的
检查下应用模式，在备库上
SQL> select recovery_mode from v$archive_dest_status where dest_id=2;
RECOVERY_MODE
-----------------------
IDLE
我明明启动了实时应用啊，怎么上面的模式会不对呢？停止实时应用看看
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active
一直报这个错，请问是什么原因啊？解决不了啊，还请知道的指导下。

千问 · 2011-6-30 12:30:01

select process,status from v$managed_standby;
把结果贴出来看看

千问 · 2011-6-30 12:30:01

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1677721600 bytes
Fixed Size
2084528 bytes
Variable Size
503316816 bytes
Database Buffers 1157627904 bytes
Redo Buffers
14692352 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCHCONNECTED
ARCHCONNECTED

千问 · 2011-6-30 12:30:01

隔了分把钟之后再查
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCHCONNECTED
ARCHCONNECTED
RFS IDLE
RFS IDLE
没有mpr进程，所以应用不了，但我不知道为什么没有这个进程，我记得我曾经看到过有的。

千问 · 2011-6-30 12:30:01

把你的spfile贴出来看看？

千问 · 2011-6-30 12:30:01

先看备库的
$ cat /oracle/product/10.2.0/db_1/dbs/inithis1.ora
his2.__db_cache_size=637534208
his1.__db_cache_size=1157627904
his2.__java_pool_size=16777216
his1.__java_pool_size=16777216
his2.__large_pool_size=16777216
his1.__large_pool_size=16777216
his2.__shared_pool_size=989855744
his1.__shared_pool_size=469762048
his2.__streams_pool_size=0
his1.__streams_pool_size=0
*.audit_file_dest='/oracle/product/10.2.0/admin/his/adump'
*.background_dump_dest='/oracle/product/10.2.0/admin/his/bdump'
*.cluster_database_instances=2
*.cluster_database=TRUE
*.compatible='10.2.0.3.0'
*.control_files='/dev/racc_ctl1_512M','/dev/racc_ctl2_512M'#Restore Controlfile
*.core_dump_dest='/oracle/product/10.2.0/admin/his/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='acc'
*.db_recovery_file_dest_size=3221225472
*.db_unique_name='his'
*.FAL_CLIENT='his'
*.FAL_SERVER='acc'
his1.instance_number=1
his2.instance_number=2
*.job_queue_processes=10
his1.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.1.13) (PORT = 1521))'
his2.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.1.14) (PORT = 1521))'
*.log_archive_config='dg_config= (acc,his)'
his1.LOG_ARCHIVE_DEST_1='location=/arch1 valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=his'
his2.LOG_ARCHIVE_DEST_1='location=/arch2 valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=his'
*.LOG_ARCHIVE_DEST_2='SERVICE=acc LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=acc'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=2097152000
*.processes=800
*.remote_listener='LISTENERS_his'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.sessions=885
*.sga_target=1677721600
*.STANDBY_FILE_MANAGEMENT='AUTO'
his2.thread=2
his1.thread=1
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
his1.undo_tablespace='UNDOTBS1'
his2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/oracle/product/10.2.0/admin/his/udump'

再看主库的
$ cat /oracle/product/10.2.0/db_1/dbs/initacc1.ora
acc1.__db_cache_size=1241513984
acc2.__db_cache_size=637534208
acc1.__java_pool_size=16777216
acc2.__java_pool_size=16777216
acc1.__large_pool_size=16777216
acc2.__large_pool_size=16777216
acc1.__shared_pool_size=385875968
acc2.__shared_pool_size=989855744
acc1.__streams_pool_size=0
acc2.__streams_pool_size=0
*.audit_file_dest='/oracle/product/10.2.0/admin/acc/adump'
*.background_dump_dest='/oracle/product/10.2.0/admin/acc/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.3.0'
*.control_files='/dev/racc_ctl1_512M','/dev/racc_ctl2_512M'
*.core_dump_dest='/oracle/product/10.2.0/admin/acc/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='acc'
*.db_recovery_file_dest_size=3221225472
*.fal_client='acc'
*.fal_server='his'
acc1.instance_number=1
acc2.instance_number=2
*.job_queue_processes=10
acc1.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.1.8) (PORT = 1521))'
acc2.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.1.9) (PORT = 1521))'
*.log_archive_config='dg_config=(acc,his)'
acc1.log_archive_dest_1='location=/arch1'
acc2.log_archive_dest_1='location=/arch2'
*.log_archive_dest_1='location=/arch2 valid_for=(all_logfiles,all_roles) db_unique_name=acc'
*.log_archive_dest_2='service=his valid_for=(online_logfile,primary_role) db_unique_name=his'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=2097152000
*.processes=800
*.remote_listener='LISTENERS_ACC'
*.remote_login_passwordfile='exclusive'
*.sessions=885
*.sga_target=1677721600
*.standby_file_management='AUTO'
acc2.thread=2
acc1.thread=1
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
acc1.undo_tablespace='UNDOTBS1'
acc2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/oracle/product/10.2.0/admin/acc/udump'

千问 · 2011-6-30 12:30:01

主库：
select dest_id,status,error from v$archive_dest where dest_id=2;
结果贴出来
备库的alert贴出来

千问 · 2011-6-30 12:30:01

原帖由 howard_zhang 于 2011-1-12 16:28 发表
主库：
select dest_id,status,error from v$archive_dest where dest_id=2;
结果贴出来
备库的alert贴出来

SQL> select dest_id,status,error from v$archive_dest where dest_id=2;
DEST_ID STATUS
---------- ---------
ERROR
-----------------------------------------------------------------
2 VALID
备库的alert见附件

alert_his1.rar(9.92 KB, 下载次数: 22)2011-1-12 16:43 上传点击文件名下载附件

千问 · 2011-6-30 12:30:01

主库：
acc1.log_archive_dest_1='location=/arch1' valid_for=(all_logfiles,all_roles) db_unique_name=acc'
acc2.log_archive_dest_1='location=/arch2' valid_for=(all_logfiles,all_roles) db_unique_name=acc'
*.log_archive_dest_2='service=his valid_for=(online_logfile,primary_role) db_unique_name=his'

千问 · 2011-6-30 12:30:01

Wed Jan 12 15:21:32 2011
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Wed Jan 12 15:21:32 2011
Recovery interrupted!
Wed Jan 12 15:21:32 2011
Errors in file /oracle/product/10.2.0/admin/his/bdump/his1_mrp0_901216.trc:
ORA-00308: cannot open archived log '/arch2/2_1523_715014709.arc'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
问题在于你传输过来的日志放到了哪里？
你备库的归档日志的位置目录检查一下