首页 > 代码库 > 总结搭建Oracle11g DG踩的坑

总结搭建Oracle11g DG踩的坑

2024-09-02 21:29:28   220人阅读

 

此次的操作环境是Oracle11g 单实例，os为Linux，采用duplicate在线创建物理备库

primary上设置相关参数

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(ora11g,stdb)‘;alter system set log_archive_dest_2=‘SERVICE=DB_DG2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=stdb‘; 
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET FAL_SERVER=ORA11G02;
ALTER SYSTEM SET FAL_CLIENT=ORA11G01;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
alter system set LOG_ARCHIVE_DEST_1=                 
 ‘LOCATION=/data0/u01/app/oracle/arch        
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=ora11g‘ scope=spfile;       
alter system set log_archive_format=‘%t_%s_%r.arch‘ scope=spfile;

添加standby redo log

alter database add standby logfile thread 1 group 4(‘/data0/u01/app/oracle/oradata/ora11g/standby_redo04.log‘) size 50m; 
alter database add standby logfile thread 1 group 5(‘/data0/u01/app/oracle/oradata/ora11g/standby_redo05.log‘) size 50m; 
alter database add standby logfile thread 1 group 6(‘/data0/u01/app/oracle/oradata/ora11g/standby_redo06.log‘) size 50m; 
alter database add standby logfile thread 1 group 7(‘/data0/u01/app/oracle/oradata/ora11g/standby_redo07.log‘) size 50m;

备库安装完软件配置好环境变量

然后是配置监听文件listener.ora和tnsnames.ora

创建备库的命令为

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET "db_unique_name"="jjdb"
    SET LOG_ARCHIVE_DEST_2="service=ora11g02 ASYNC REGISTER
     VALID_FOR=(online_logfile,primary_role)"
    SET FAL_CLIENT="ora11g02" 
    SET FAL_SERVER="ora11g01" 
  NOFILENAMECHECK;

针对以上过程，rman会自动拷贝server端的参数文件到standby端，然后启动到nomount状态，还原控制文件，并拷贝所有的数据文件、临时表空间和归档日志到备库。然后进行recovery

在将备库启动到nomount状态是使用pfile即：startup nomount pfile=initorcl11g.ora

否则由于spfile被占用而报：RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause
然后在primary端（standby端均可）登录rman

 rman target sys/oracle@ORA11G01 auxiliary sys/oracle@ORA11G02

在创建备库期间，变更完参数后，从库会重启，报错：

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

首先确定口令文件是从主库端拷贝过来的，文件名字以及用户名和密码都是正确的

使用tnsping检查网络也都互通

最后查看从库监听状态，只有一个orcl11g实例（对应$ORACLE_SID）

但是linstener.ora 中的SID_NAME指定的非$ORACLE_SID

改正监听文件后开始创建备库

创建完成后查看备机的数据库状态

SQL> SELECT DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE FROM V$DATABASE;

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MOUNTED              MAXIMUM PERFORMANCE
# standby
SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
         3         ONLINE  /data0/u01/app/oracle/fast_recovery_area/STDB/onlinelog/o1_mf_3_d8byvvoq_.log
         2         ONLINE  /data0/u01/app/oracle/fast_recovery_area/STDB/onlinelog/o1_mf_2_d8byvvn0_.log
         1         ONLINE  /data0/u01/app/oracle/fast_recovery_area/STDB/onlinelog/o1_mf_1_d8byvvl6_.log

此时迷惑我的问题发生了，rman是不会拷贝redo日志文件的，那么此时查看到的redo日志文件是控制文件记录的，但是主上的redo log路径并不是这个，可视主机开启了闪回区，所以redo log在闪回区也有存在

所以可以推断控制文件记录的redo log的路径是: 闪回区路径/db_uniq_name/onlinelog,实际上在备库上并不存在这些redo log file

备库创建redo log和standby redo log

开始启动备库到实时应用日志

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> SELECT DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE FROM V$DATABASE;

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY READ ONLY            MAXIMUM PERFORMANCE

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> SELECT DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE FROM V$DATABASE;

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE

备库上查看 archive log list

归档路径状态异常，查看使用的是LOG_ARCHIVE_DEST_3

SQL>  col DEST_NAME format a30
SQL> col DESTINATION format a30
SQL> SELECT dest_name, status, destination FROM v$archive_dest;

DEST_NAME                      STATUS    DESTINATION
------------------------------ --------- ------------------------------
LOG_ARCHIVE_DEST_1             BAD PARAM /data0/u01/app/oracle/arch
LOG_ARCHIVE_DEST_2             BAD PARAM ora11g02
LOG_ARCHIVE_DEST_3             VALID     USE_DB_RECOVERY_FILE_DEST

官方文档上对状态为BAD PARAM的解释为 A parameter error occurred; refer to error data.

select dest_id,status,error from v$archive_dest ;  # 未发现错误信息
查看参数发现是db_uniq_name设置错误
更正后重启数据库后
路径1和2的状态均为valid
但是archive log list看到的仍旧是
Archive destination            USE_DB_RECOVERY_FILE_DEST
官方文档解释：If you configure a Fast Recovery Area (by setting the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters) and do not specify any local archive destinations, the database automatically selects the Fast Recovery Area as a local archive destination and sets LOG_ARCHIVE_DEST_1 to USE_DB_RECOVERY_FILE_DEST.
但是查看发现闪回区路径下和LOG_ARCHIVE_DEST_1下均产生归档日志文件

至此搭建完毕

 

总结搭建Oracle11g DG踩的坑


联系
我们