2012 国庆在家dataguard 碰到很多问题 （监听 + 密码 + 主库归档路径）

今天 配dataguard 的时候，碰到很多问题：

Case 1:

1：ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 12514.

原因：
是主备库的listener 没有配好：

解决方法：

[oracle@aoracle admin]$ more listener.ora
SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = PLSExtProc)
          (ORACLE_HOME = /u01/pp/oracle/product/10.2/db_1)
          (PROGRAM = extproc)
        )
    (SID_DESC =
    (GLOBAL_DBNAME = ezhou)
    (ORACLE_HOME = /u01/pp/oracle/product/10.2/db_1)
           (SID_NAME = ezhou)
       )
      )
     
看一下tnsname.ora:

[oracle@aoracle admin]$ more tnsnames.ora
primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.118.128)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ezhou)
    )
  )
standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.118.149)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ezhou)
    )
  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
 
然后启动lsnrctl stop / lsnrctl start 就可以了。

--------

Case2:

因为我的alert 日志是同时打开的，看到：

报错变了：

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
It may be necessary to define the DB_ALLOWED_LOGON_VERSION
initialization parameter to the value "10".  Check the
manual for information on this initialization parameter.
------------------------------------------------------------
Thu Oct  4 16:38:04 2012
Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_arc0_24145.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 16191.

原因：
最后发现是密码文件有.ora后缀导致的。应该是orcle不人性的一个地方或者一个bug吧。

主备库上分别重建下密码文件，一切正常了。

[oracle@aoracle dbs]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=30 force=y  ignorecase=y

特别说明：
不要想当然 认为上面的是大写 sid 还是其他的，要看oracle 怎么认，结果 生成出来的文件是小写的。悲剧，看网上有很多说的是要大写，结果是不对的。

然后再scp 到备库，就可以了。

-------------

Case3:

我执行了一下 归档：

SQL> select sequence# from v$log;

 SEQUENCE#
----------
         2
         3
         4

SQL> alter system switch logfile;

System altered.

SQL> select sequence# from v$log;

 SEQUENCE#
----------
         5
         3
         4
        
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 4 (4)

原因是primay server 上没有设置归档地方，

虽然archive log list 上有个地方，但是要是要看spfile文件中的设置：

下面改一下：
添加下面：

*.log_archive_dest_1='location=/u01/rman/ezhou_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

同样在standby 的initezhou 上也要添加：

*.log_archive_dest_1='location=/u02/ezhou/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

create spfile from pfile,

重新启动，就可以了。

-----------------

Case5:
虽然archive log 是过去了，
但是alert 日志是报错的：

ORA-00313: open failed for members of log group 6 of thread 1
ORA-00312: online log 6 thread 1: '/u02/ezhou/redo06.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
RFS[1]: Unable to open standby log 6: 313
RFS[1]: No standby redo logfiles created

原因，

没有建standby 日志：

解决方法;

SQL> alter database add standby logfile group 4 ('/u02/ezhou/redo04.dbf') size 50m;

SQL> alter database add standby logfile group 5 ('/u02/ezhou/redo05.dbf') size 50m;

SQL> alter database add standby logfile group 6 ('/u02/ezhou/redo06.dbf') size 50m;