检查过程
Step 1 Identify the existing archived redo log files.
在备库上查询如下:
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Step 2 Force a log switch to archive the current online redo log file.
在主库上切换日志如下:
ALTER SYSTEM SWITCH LOGFILE;
Step 3 Verify the new redo data was archived on the standby database.
在备库上查询如下:
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Step 4 Verify that received redo has been applied.
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;
[@more@]
中间遇到的问题:
1 没有启动listener,造成备库上查不到归档日志。
2 备库上的日志没有应用
On the standby database, issue the following command to start Redo Apply:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
RECOVERY NEEDED
原因是日志未应用,
查主机上的日志
......
Errors in file /opt/app/oracle/diag/rdbms/sales/sales/trace/sales_arc5_454726.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC5]: Heartbeat failed to connect to standby 'sales2'. Error is 16191.
Sat Nov 15 09:46:13 2008
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
......
Fatal NI connect error 12541, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.73)(PORT=1521))(CONNECT_DATA=(SE
RVICE_NAME=sales)(CID=(PROGRAM=oracle)(HOST=aixstudy)(USER=oracle))))
VERSION INFORMATION:
TNS for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production
TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.1.0.6.0
- Production
Time: 29-NOV-2008 05:24:15
Tracing not turned on.
Tns error struct:
ns main err code: 12541
TNS-12541: TNS:no listener
ns secondary err code: 12560
nt main err code: 511
TNS-00511: No listener
nt secondary err code: 79
nt OS err code: 0
Error 12541 received logging on to the standby
Check whether the listener is up and running.
Errors in file /opt/app/oracle/diag/rdbms/sales/sales/trace/sales_arcq_286870.trc:
ORA-12541: TNS:no listener
PING[ARCq]: Heartbeat failed to connect to standby 'sales2'. Error is 12541.
Sat Nov 29 05:29:22 2008
..........................
***********************************************************************
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=
(
ADDRESS=
(PROTOCOL=TCP)
(HOST=10.10.10.73)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=sales)
(CID=(PROGRAM=oracle)(HOST=aixstudy)(USER=oracle))
)
)
VERSION INFORMATION:
TNS for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production
TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.1.0.6.0
- Production
Time: 01-DEC-2008 12:37:31
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 12514 received logging on to the standby
Errors in file /opt/app/oracle/diag/rdbms/sales/sales/trace/sales_arcq_286870.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect desc
riptor
PING[ARCq]: Heartbeat failed to connect to standby 'sales2'. Error is 12514.
Mon Dec 01 12:46:18 2008
LNS1 started with pid=50, OS id=516208
Mon Dec 01 12:46:22 2008
Thread 1 advanced to log sequence 93
Current log# 3 seq# 93 mem# 0: /opt/oradata/sales/redo03.log
LNS: Standby redo logfile selected for thread 1 sequence 93 for destination LOG_ARCH
IVE_DEST_2
Mon Dec 01 12:46:25 2008
ARCl: Standby redo logfile selected for thread 1 sequence 92 for destination LOG_ARC
HIVE_DEST_2
Mon Dec 01 14:05:42 2008
Thread 1 advanced to log sequence 94
Current log# 1 seq# 94 mem# 0: /opt/oradata/sales/redo01.log
Mon Dec 01 14:05:43 2008
LNS: Standby redo logfile selected for thread 1 sequence 94 for destination LOG_ARCH
IVE_DEST_2
Mon Dec 01 14:52:38 2008
ARCq: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARCq: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
PING[ARCq]: Error 3113 when pinging standby sales2.
Mon Dec 01 14:52:38 2008
LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Errors in file /opt/app/oracle/diag/rdbms/sales/sales/trace/sales_lns1_516208.trc:
ORA-03135: connection lost contact
LGWR: I/O error 3135 archiving log 1 to 'sales2'
Errors in file /opt/app/oracle/diag/rdbms/sales/sales/trace/sales_lns1_516208.trc:
ORA-03135: connection lost contact
Errors in file /opt/app/oracle/diag/rdbms/sales/sales/trace/sales_lns1_516208.trc:
ORA-03135: connection lost contact
background_dump_dest
/opt/app/oracle/diag/rdbms/sales2/sales/trace
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
MRP0: Background Media Recovery terminated with error 1110
Errors in file /opt/app/oracle/diag/rdbms/sales2/sales/trace/sales_mrp0_520194.trc:
ORA-01110: data file 1: '/opt/oradata/sales2/system01.dbf'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/opt/oradata/sales2/system01.dbf'
Errors in file /opt/app/oracle/diag/rdbms/sales2/sales/trace/sales_mrp0_520194.trc:
ORA-01110: data file 1: '/opt/oradata/sales2/system01.dbf'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/opt/oradata/sales2/system01.dbf'
MRP0: Background Media Recovery process shutdown (sales)
Completed: alter database recover managed standby database disconnect from session
Mon Dec 01 14:57:50 2008
Using STANDBY_ARCHIVE_DEST parameter default value as /ww/arch1/sales/
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 483470
RFS[1]: Identified database type as 'physical standby'
RFS LogMiner: Client disabled from further notification
Mon Dec 01 15:07:55 2008
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
看来是参数文件中的sale造成的
在两个主机上修改参数文件,注释掉以下内容:
*.db_file_name_convert='sales','sales2'
在主机上重建 spfile,并重启数据库;
在备机上重建 spfile, 并进入管理模式
以下是备机上的操作记录
$ export ORACLE_SID=sales
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Dec 1 15:43:35 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> create spfile from pfile;
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 329859072 bytes
Fixed Size 2137944 bytes
Variable Size 201326760 bytes
Database Buffers 121634816 bytes
Redo Buffers 4759552 bytes
SQL> alter database mount standby database;
Database altered.
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
2 ORDER BY SEQUENCE#;
SEQUENCE# APP
---------- ---
81 YES
82 YES
83 YES
84 YES
85 NO
86 NO
87 NO
88 NO
89 NO
90 NO
91 NO
SEQUENCE# APP
---------- ---
92 NO
93 NO
13 rows selected.
SQL> /
SEQUENCE# APP
---------- ---
81 YES
82 YES
83 YES
84 YES
85 YES
86 YES
87 YES
88 YES
89 YES
90 YES
91 YES
SEQUENCE# APP
---------- ---
92 YES
93 YES
13 rows selected.
好,至此,archives are not applying 日志不能apply的问题解决.
在备机上查看切换状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/271063/viewspace-1014086/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/271063/viewspace-1014086/