Oracle 11g Aix 雙機 物理Standby配置 04

檢查過程

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