oracle DG 断开恢复，应用的是ASM存储，监听在grid用户下

一、背景

角色	IP	dbuniquename	服务
主库	192.168.10.11	ORCL	ORCL
备库	192.168.10.12	ORCLDG	ORCLDG

oracle dg架构因为备库宕机导致主库无法自动删除归档，ASM存储撑满，无法写入数据，应用程序无法登录；

主库无法写入数据时间为2020.8.30日，备库宕机时间为2020.7.24日，主从差异太大，备库未应用日志部分在主库找不到，

因此决定：

1.先删除主库归档，全备等，释放空间，让业务正常运行

2.重新拉取全备到备库上，恢复主从同步

二、删除主库ASM存储归档和多余备份

1.删除备份

[oracle@host1 ~]$ rman target /
Recover Manager: Release 11.2.0.4.0 - Production on Mon Aug 31 17:40:58 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=2188803816)

RMAN> list backup;
...
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3111    Incr 0  49.39G     DISK        02:41:30     2020/08/30 03:53:37
        BP Key: 3111   Status: AVAILABLE  Compressed: YES  Tag: LEVEL 0
        Piece Name: +ZHYDDG/backupset/level0/level0_ORCL_3114_1_1av948f7.bak
  List of Datafiles in backup set 3111

RMAN> delete backupset 3109,3110;--删除最后一个0级备份之前的所有备份
RMAN> crosscheck backup; --检查备份物理文件状态
RMAN> delete obsolete;  --只能删除过期备份
RMAN> delete expired backup; --只能删除物理不存在的备份记录

2.删除归档

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
RMAN> 或者删除指定时间之前的archivelog：  
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';(指定删除7天前的归档日志)  
#但是ASM归档日志用上面的命令有时无法删除日志释放空间（珠海捷安项目ASM就不可以删除日志）
RMAN>delete archivelog until time 'sysdate-1' ; --删除一天前的归档
或者
RMAN>delete archivelog until sequence 97928;  --删除编号为97928之前的归档
RMAN> crosscheck archivelog all;

主库释放ASM空间后，应用就可以正常访问了

3.清空备库ASM空间

备库ASM中也存在完整备份和归档，按照上面方法清理并释放空间

三、恢复DG主从复制

1.检查从库的参数文件和密码文件是否正常

参数文件找不到的话可以用spfile生成

[oracle@host2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 31 18:37:33 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>
SQL> create pfile from spfile;

File created.

2.测试主备库 tnsping

主库
tnsping primary
tnsping standby
备库
tnsping primary
tnsping standby

3.备库在nomount模式下测试密码文件是否可用

rman target sys/***@standby
#或者
sqlplus sys/***@standby as sysdba

报错：
Instance "ORCL", status BLOCKED
因为grid的监听上是自动注册和静态注册两种都生效的注册方式
静态注册模式下实例一般为 UNKNOWN
动态注册模式下，实例no started时 不会注册实例
实例nomount状态下，为BLOCKED，禁止用户登录
实例mount或started状态下，为READY，可登录

[grid@host2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-AUG-2020 18:53:22

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC15212)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-AUG-2020 22:31:39
Uptime                    0 days 20 hr. 21 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/host2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC15212)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.31.250.27)(PORT=15212)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.31.250.27)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCLdg" has 2 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

#解决办法
修改备库TNS standby 添加(UR=A)
[oracle@host2 admin]$ more tnsnames.ora
standby=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.250.27)(PORT = 15212))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCLdg)
    (UR=A)  #加上这个就可以登录了
    )
  )

3.备库在no started模式下测试密码文件是否可用

rman target sys/***@standby
#或者
sqlplus sys/***@standby as sysdba
报错
ORA-01017: invalid username/password; logon denied
#因为数据库为no start模式，所以无法自动注册到grid的监听程序上，
grid的监听程序只有静态注册的服务
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
因为grid监听指定的ORACLE_HOME=/u01/app/11.2.0/grid
所以无法找到正确的密码文件和实例存放位置
解决办法：
修改grid监听程序中的ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 重启监听

[grid@host2 ~]$ cd $ORACLE_HOME
[grid@host2 grid]$ cd network/admin/
[grid@host2 admin]$ vim listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/11.2.0/grid)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLdg)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      #(ORACLE_HOME = /u01/app/11.2.0/grid)
      (SID_NAME = ORCL)
    )
  )
  
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC15212))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.250.27)(PORT = 15212))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

#lsnrctl stop
#lsnrctl start
再次试着密码登录standy，可以连接到一个no started数据库

 

4.复制主库到备库上

 

在备库上执行：

[oracle@host2 ~]$rman target sys/***@primary auxiliary sys/***@standby
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 31 19:57:47 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=2188803816)
connected to auxiliary database: ORCL (not start)

#执行复制
RMAN>duplicate target database for standby from active database nofilenamecheck;
...
复制完成
备库自动为mount状态

5.将grid的监听ORACLE_HOME改回来，重启监听，并自动注册standby

[grid@host2 admin]$ vim listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/11.2.0/grid)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLdg)
      #(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (ORACLE_HOME = /u01/app/11.2.0/grid)
      (SID_NAME = ORCL)
    )
  )
  
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC15212))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.250.27)(PORT = 15212))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

#lsnrctl stop
#lsnrctl start

重启监听后，standby不会自动注册到gird上，
所以密码登录standby时是登不上去的，主库日志也会报standby不可连接

查看standby 的tnsnames.ora
[oracle@host2 admin]$ more tnsnames.ora
standby=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.250.27)(PORT = 15212))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCLdg)
  #    (UR=A)
    )
  )

primary=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.250.3)(PORT = 15212))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

#一会用ORCL手动将数据库注册到grid的监听上
ORCL=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.250.27)(PORT = 15212))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLdg)
    )
  )
[oracle@host2 admin]$ [

免密登录到standby，注册主句库到监听

sqlplus / as sysdba

SQL> show parameter local_l
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string

SQL>alter  system set local_listener='ORCL'; （用tnsname中的ORCL注册）

SQL> alter system register;（通知PMON立即将实例注册到监听）

#在grid用户下
lsnrctl reload lsnr2（重新加载）
lsnrctl status
应该会发现已经注册成功，实例为READ状态
Service "ORCLdg" has 2 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ORCL", status READY, has 1 handler(s) for this service...

##密码可以成功登录standby库
##主库的归档可以传输到备库

6.备库启动日志应用

 

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

#查看日志应用状态
SQL>select sequence#,name,applied from v$archived_log;