DG常用启动方法与异常查找

原创已于 2024-12-23 17:00:32 修改 · 1k 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #DG

于 2024-12-23 16:18:15 首次发布

oracle 专栏收录该内容

12 篇文章

订阅专栏

--查看主库的状态：

select a.inst_id,a.db_unique_name,a.database_role,

a.protection_level,a.protection_mode,a.open_mode,a.log_mode,a.switchover_status,

b.host_name,b.thread#

from gv$database a left join gv$instance b

on a.inst_id=b.inst_id order by a.inst_id;

---性能模式:

select name,db_unique_name,protection_mode from v$database;

--SESSIONS ACTIVE--正常状态

startup;

--startup nomount;

---alter database mount standby database;

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

alter database recover managed standby database using current logfile disconnect;

lsnrctl status

lsnrctl start LISTENER

lsnrctl start LISTENER_stb

GAP确认：

select case when gapQtyList<3 then 0 else gapQtyList end gapQtyList from(

Select max(case when a.APPLIED!='YES' then a.SEQUENCE# else 0 end)-

max(case when a.APPLIED='YES' then a.SEQUENCE# else 0 end) gapQtyList

From v$archived_log a

Where a.COMPLETION_TIME>sysdate-1);

select process,status,sequence#,thread# from v$managed_standby where process='MRP0';

select PROCESS,STATUS,SEQUENCE#,BLOCKS from V$MANAGED_STANDBY;

---确认模式与状态:

set pagesize 300;

set linesize 300;

col OPEN_MODE format a10; col PROTECTION_MODE format a10; col PROTECTION_LEVEL format a10;

col SWITCHOVER_STATUS format a10;col FORCE_LOGGING format a10;col PRIMARY_DB_UNIQUE_NAME format a10;

select dbid,Name,log_mode,a.CONTROLFILE_TYPE,a.OPEN_MODE,a.PROTECTION_MODE,

a.PROTECTION_LEVEL,a.REMOTE_ARCHIVE,a.DATABASE_ROLE,a.SWITCHOVER_STATUS,a.FORCE_LOGGING,a.PRIMARY_DB_UNIQUE_NAME from v$database a;

select round(sum(bytes)/1024/1024/1024,4) sizeGb from dba_segments;

BEGIN

DBMS_OUTPUT.PUT_LINE('check ADG need archive Log:');

END;

set linesize 600

col SESSION_KEY format 999999

col INPUT_TYPE format a10

col START_TIME format a20

col END_TIME format a20

col status format a30

col elapHour format 999.99

select SESSION_KEY,INPUT_TYPE,STATUS,to_char(START_TIME, 'yyyy-mm-dd hh24:mi') START_TIME,

to_char(END_TIME, 'yyyy-mm-dd hh24:mi') END_TIME,round(ELAPSED_SECONDS / 3600,2) elapHour

from v$rman_backup_job_details

where to_char(START_TIME, 'yyyy-mm-dd hh24:mi') > to_char(sysdate-7,'yyyy-mm-dd hh24:mi') order by SESSION_KEY;

BEGIN

DBMS_OUTPUT.PUT_LINE('check DG backup is or not health:');

END;

col END_TIME format a20

col INPUT_BYTES_DISPLAY format a15

select input_bytes_display,start_time,end_time,status,input_type,ELAPSED_SECONDS

from V$RMAN_BACKUP_JOB_DETAILS where input_type ='DB INCR' order by start_time;

BEGIN

DBMS_OUTPUT.PUT_LINE('check 31 Day archive Log Size:');

END;

SQL> show parameter log_archive_dest_state

在主库执行SQL> alter system set log_archive_dest_state_2= enable;

这个命令式手动触发主库区尝试连接备库。

其实这种情况下，只要保证主备库之间的网络和配置是正确的。

dataguard会自动恢复这个错误。这个周期默认是300秒，

3.2.1 关闭standby库的dataguard恢复进程

alter database recover managed standby database cancel;

3.2.9 启动standby库dataguard恢复进程

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

---check 最新的archivelog是否应用：

SELECT max(sequence#) from v$archived_log where applied='YES';

SQL> show parameter log_archive_dest_state

6.生产库切换日志

Alter system switch logfile;

3、取消备库的自动恢复

SQL> alter database recover managed standby database cancel;

4、打开日志实时应用状态模式 `

---巴西使用以下命令同步日志:

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

SQL> alter database recover managed standby database disconnect from session;

Select process,status,sequence# from v$managed_standby;

4、查看日志应用到哪个组

SQL> select max(SEQUENCE#) from v$archived_log where applied='YES';

5.RFS没有启动：

SQL> show parameter LOG_ARCHIVE_DEST_

select error from v$archive_dest where target='STANDBY';

原因： lsnrctl status 没有开启，造成异常：

oracle@BRAIMESDG:~> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-DEC-2024 00:21:33

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=BRAIMESDG.a-data.com)(PORT=1521)(IP=FIRST)))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

Linux Error: 111: Connection refused

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=BRAIMES_std.a-data.com)(PORT=1521)(IP=FIRST)))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

Linux Error: 111: Connection refused

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

Linux Error: 111: Connection refused

oracle@BRAIMESDG:~>

oracle@BRAIMESDG:~> lsnrctl start

备库上查看：

SQL> Select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#

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

ARCH CLOSING 306704

ARCH CLOSING 307492

ARCH CONNECTED 0

ARCH CLOSING 306703

RFS IDLE 0

RFS IDLE 307493

RFS IDLE 0

RFS IDLE 306705

RFS IDLE 0

MRP0 APPLYING_LOG 306705

PROCESS STATUS SEQUENCE#

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

RFS IDLE 0

13 rows selected.

PROCESS STATUS SEQUENCE#

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

ARCH CONNECTED 0

RFS IDLE 1269309

RFS IDLE 0

MRP0 WAIT_FOR_LOG 1269309

PROCESS STATUS SEQUENCE#

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

RFS IDLE 0

主库上查看：

SQL> Select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#

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

ARCH CLOSING 1269287

ARCH CLOSING 1269288

ARCH CLOSING 1269289

ARCH CLOSING 1269290

ARCH CLOSING 1269291

ARCH CLOSING 1269292

ARCH CLOSING 1269293

ARCH CLOSING 1269294

LNS WRITING 1269309

31 rows selected.

1① 启动实时应用

2 物理：ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

3 逻辑：ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

alter database start logical standby apply immediate;

4② alter system set log_archive_dest_state_2='defer';

5alter system switch logfile;

6alter system set log_archive_dest_state_2='enable';

7alter system switch logfile;

8③ 重启备库

1> DG库启动、关闭标准操作

Dataguard关闭

1)、先取消日志应用alter database recover managed standby database cancel;

2)、正常关闭DG库Shutdown immediate;

Dataguard开启

1)、数据库先启动到nomount状态1 startup nomount;

2)、然后数据库在启动到standby mount状态alter database mount standby database;

3)、最后开启日志应用alter database recover managed standby database disconnect from session using current logfile;

检查初始化参数：
- 在主数据库上，检查LOG_ARCHIVE_DEST_和LOG_ARCHIVE_DEST_STATE_参数，确保它们指向备用数据库，并且状态为ENABLE。
- 在备用数据库上，检查LOG_ARCHIVE_DEST_参数，确保它配置了接收来自主数据库的归档日志。
手动启动RFS： ALTER SYSTEM START LOGICAL STANDBY APPLY;
- 可以尝试手动启动RFS进程，使用命令ALTER SYSTEM START LOGICAL STANDBY APPLY;（对于逻辑备用数据库）或ALTER SYSTEM SWITCH LOGFILE;（对于物理备用数据库）。