dg检查

[TOC]

## 1. 启动实时同步应用

```sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
```

## 2. 查看归档传送链路状态

```sql
SET LINESIZE 220;  -- 设置每行的最大字符数
SET PAGESIZE 10000;  -- 设置每页的行数
SET LONG 999999999;  -- 设置长文本的最大字符数
COL DEST_NAME FOR A50;  -- 设置 DEST_NAME 列的宽度为 50
COL DESTINATION FOR A50;  -- 设置 DESTINATION 列的宽度为 50

SELECT DEST_NAME, STATUS, DATABASE_MODE, DESTINATION, ERROR 
FROM V$ARCHIVE_DEST_STATUS  
WHERE DEST_ID < 3;  -- 查询归档目的地状态，过滤出有效的目的地
```

## 3. 备库查看日志间隙

```sql
SELECT STATUS, GAP_STATUS 
FROM V$ARCHIVE_DEST_STATUS 
WHERE DEST_ID = 2;  -- 查询指定目的地的状态和日志间隙
```

## 4. 备库查看进程状态

```sql
SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS 
FROM V$MANAGED_STANDBY 
WHERE CLIENT_PROCESS = 'LGWR' OR PROCESS = 'MRP0';  -- 查询正在进行重做日志应用的进程
```

## 5. 查看归档应用状态

```sql
SELECT THREAD#, SEQUENCE#, 'Last Applied : ' AS Logs, TO_CHAR(NEXT_TIME, 'DD-MON-YYYY:HH24:MI:SS') AS Time  
FROM V$ARCHIVED_LOG  
WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = 'YES')  
--查询最后应用的归档日志
UNION
SELECT THREAD#, SEQUENCE#, 'Last Received : ' AS Logs, TO_CHAR(NEXT_TIME, 'DD-MON-YYYY:HH24:MI:SS') AS Time  
FROM V$ARCHIVED_LOG 
WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG);  -- 查询最后接收的归档日志
```

## 6. 查询数据库角色、打开模式、保护模式和保护级别

```sql
SELECT DATABASE_ROLE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;
```

## 7. 查询参数设置，检查数据库文件名转换参数

```sql
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE '%db_file_name_convert%';
```

## 8. 查询未应用的归档日志

```sql
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG WHERE APPLIED = 'NO';
```

## 9. 查询运输延迟和应用延迟

```sql
SELECT 
    NAME, 
    VALUE, 
    UNIT 
FROM 
    V$DATAGUARD_STATS 
WHERE 
    NAME IN ('transport lag', 'apply lag');
```

## 10. 查询最后应用的归档日志序列号

```sql
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = 'YES';
```

## 11. 查询 Data Guard 状态信息

```sql
SELECT MESSAGE, TIMESTAMP FROM V$DATAGUARD_STATUS;
```

## 12. 再次查看归档应用状态

```sql
SELECT THREAD#, SEQUENCE#, 'Last Applied : ' AS Logs, TO_CHAR(NEXT_TIME, 'DD-MM-YYYY:HH24:MI:SS') AS Time 
FROM V$ARCHIVED_LOG 
WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = 'YES')  -- 查询最后应用的归档日志
UNION
SELECT THREAD#, SEQUENCE#, 'Last Received : ' AS Logs, TO_CHAR(NEXT_TIME, 'DD-MON-YYYY:HH24:MI:SS') AS Time 
FROM V$ARCHIVED_LOG 
WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG);  -- 查询最后接收的归档日志
```

## 13. 在主库中启动数据库

```sql
-- 主库
STARTUP;
```

## 14. 在备库中启动数据库并挂载

```sql
-- 备库
STARTUP MOUNT;
```

## 15. 进行直到一致性点的恢复

```sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE UNTIL CONSISTENT;
```

DBLOG

导航1234

最近发表

友情链接