Oracle database DG 问题处理

DG连接

1.在主库查看数据库状态

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
calldb           OPEN

SQL> 

以下操作主备份都进行

2.查看force logging 状态

SQL> select force_logging from v$database;

FOR
---
NO

SQL> 

3.开启强制日志

SQL> alter database force logging;

Database altered.

SQL> 

4.增加Standby Redo Log

4.1仅仅显示Online Redo，不显示Standby Redo

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

         1          1       5020   52428800        512          1 YES INACTIVE             179256231 10-JUL-20    179271434 10-JUL-20


         2          1       5021   52428800        512          1 NO  CURRENT              179271434 10-JUL-20   2.8147E+14


         3          1       5019   52428800        512          1 YES  INACTIVE             179225362 10-JUL-20    179256231 10-JUL-20



SQL> 

4.2查看Redo和Standby Redo

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE  MEMBER                                IS_
---------- ------- -----------------------------------------------------------------------

         3  ONLINE         /home/oradata/calldb/redo03.log   NO

         2  ONLINE        /home/oradata/calldb/redo02.log    NO

    GROUP# STATUS  TYPE     MEMBER                 IS_
---------- ------- --------------------------------------------------------------

         1  ONLINE          /home/oradata/calldb/redo01.log  NO




SQL> 

4.3增加standby redo 大小为500m

SQL> alter database add standby logfile group 18 '/u01/app/oracle/oradata/cdaldg/cdal/onlinelog/std3_18.log' size 200M;
SQL> alter database add standby logfile group 5 '/home/oradata/calldb/standby05.log' size 500M;
SQL> alter database add standby logfile group 6 '/home/oradata/calldb/standby06.log' size 500M;
SQL> alter database add standby logfile group 7 '/home/oradata/calldb/standby07.log' size 500M;

5.在主库配置静态监听

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = calldb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = calldb)
    )
  )

6.在备库修改 log_archive_dest_1 参数设置

alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=calldbdg' scope=spfile;

7.在主库设置convert参数

SQL> show parameter convert;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
log_file_name_convert                string
SQL> 
alter system set db_file_name_convert='/home/oradata/calldb/, /u01/app/oracle/oradata/calldb/' scope=spfile;
alter system set log_file_name_convert='/home/oradata/calldb/, /u01/app/oracle/oradata/calldb/' scope=spfile;

SQL> show parameter convert;

8.复制主库的密码文件到备库（服务器上已经传过去了，省略）

scp orapwcalldb 10.2.1.193//u01/app/oracle/product/11.2.0/dbhome_1/dbs/

9.备库启动到nomount状态

alter database recover managed standby database cancel; (取消日志应用)
shutdown immediate
startup nomount

10.在备库RMAN连接到目标数据库和辅助数据库

$ rman target sys/oracle@CALLDB auxiliary sys/oracle@CALLDBDG
rman target sys/oracle@cdal_p auxiliary sys/oracle@cdal_s

使用RMAN的duplicate命令进行复制

duplicate target database for standby from active database;    #不同路径
duplicate target database for standby from active database nofilenamecheck;   #相同路径

11.复制完成后自动加载为mount状态

select status from v$instance;



alter database recover managed standby database using current logfile disconnect from session; #（启用日志应用）

12.启动备库到open状态

SQL> alter database recover managed standby database cancel;   #（取消日志应用）
SQL> alter database open; #（启动数据库到open状态）
SQL> alter database recover managed standby database using current logfile disconnect from session;#（启用日志应用）

13.主备角色状态查询

SQL> select switchover_status,database_role,open_mode from v$database;

---

如DG库不能OPEN，提示缺失日志处理如下：

查看现在DG同步状态

set lines 120
col name format a30
col value format a30
col time_computed format a30
select name,value,time_computed from V$dataguard_stats;

1.查看日志应用情况（可省略）

set linesize 200 pagesize 999
col name format a60
col applied format a3
col completion_time format a20
Select Name,Sequence#,applied,completion_time From v$archived_log Order By Sequence# Desc; 

2.对比主库和备库的归档日志（也可查看日志文件路径下的日志）

rman target /
Ramn>list archivelog all;

Rman>crosscheck archivelog all;

3.从GAP里可查看缺少那些日志

select * from V$ARCHIVE_GAP;

4.从rman里把归档日志取出来

rman target /
RMAN>copy archivelog'+fra/htdb5/ARCHIVELOG/2017_03_24/thread_1_seq_328759.4592.939510649' to'/home/oracle/arcbak/thread_1_seq_328759.4592.939510649'; 

$ scp * hotel07:/home/oracle/arcbak/

5.如控制文件里没有记录，可进行注册

[oracle@hotel07 ~]$ rman target  /
RMAN> catalog start with '/home/oracle/arcbak';

6.控制文件恢复完成，恢复归档日志

RMAN> copy archivelog  '/home/oracle/arcbak/thread_1_seq_328757.1420.939481575' to '+fra'; 

7.开启日志应用

alter database recover managed standby database using current logfile disconnect from session;

8.OPEN数据库

alter database open;