概述
set linesize 200;
set long 100000;
lsnrctl stop
lsnrctl start
select * from (select SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log order by 1 desc)
where rownum<10;
column database_role for a20;
column protection_mode for a21;
column protection_level for a18;
column open_mode for a20;
column switchover_status for a20;
select database_role,protection_mode,protection_level,open_mode,switchover_status from v$database;
SELECT PROCESS, CLIENT_PROCESS,THREAD#, SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
column name for a15;
column type for a7;
column value for a10;
show parameter LOG_ARCHIVE_CONFIG;
column name for a70;
select name from v$controlfile;
ALTER SYSTEM SWITCH LOGFILE;
set linesize 200;
set long 100000;
column name for a60;
column ARCHIVED for a10;
column APPLIED for a10;
SELECT NAME,SEQUENCE#,ARCHIVED,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
# 查看日志序列号
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
# 查看trc文件路径
SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
TRACEFILE
-------------------------------------------------------------------------------------------------------
/ora01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_ora_10539.trc
tkprof /ora01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_ora_10539.trc output=/home/oracle/aa.txt
# 如果备库报如下错误
SQL> startup
ORACLE instance started.
Total System Global Area
839282688 bytes
Fixed Size
2217992 bytes
Variable Size
574621688 bytes
Database Buffers
255852544 bytes
Redo Buffers
6590464 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+DATA/phydb/datafile/system.259.1028135687'
# 解决方法为：
SQL> shutdown immediate;
SQL> startup mount;
ORACLE instance started.
Total System Global Area
839282688 bytes
Fixed Size
2217992 bytes
Variable Size
574621688 bytes
Database Buffers
255852544 bytes
Redo Buffers
6590464 bytes
Database mounted.
SQL>
alter database recover managed standby database using current logfile disconnect from session;
Database altered.
# 注意，这一步需要等待3-4分钟
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
1.1检查ADG状态是否正常
Standby
set linesize 200;
set long 100000;
SELECT PROCESS, CLIENT_PROCESS,THREAD#, SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
SQL> SELECT PROCESS, CLIENT_PROCESS,THREAD#, SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
PROCESS
CLIENT_PROCESS
THREAD#
SEQUENCE# STATUS
------------------ ---------------- ---------- ---------- ------------------------
ARCH
ARCH
0
0 CONNECTED
ARCH
ARCH
1
27 CLOSING
ARCH
ARCH
1
29 CLOSING
ARCH
ARCH
1
28 CLOSING
MRP0
N/A
1
30 APPLYING_LOG
RFS
UNKNOWN
0
0 IDLE
RFS
ARCH
0
0 IDLE
RFS
UNKNOWN
0
0 IDLE
RFS
ARCH
0
0 IDLE
RFS
LGWR
1
30 IDLE
# Note：RFS的 STATUS 为 idle。MRP0的 STATUS 为 APPLYING_LOG。
Primary
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
RECOVERY_MODE
MANAGED REAL TIME APPLY
Note：RECOVERY_MODE为MANAGED REAL TIME APPLY
1.2 检查是否有gap
select * from v$archive_gap;
Note：如果存在gap，可以从主库复制传输缺失的归档文件到备库，并注册
1.3停掉job（primary）
主库停止运行的JOB，并设置参数：
Sql>
ALTER SYSTEM SET job_queue_processes=0 SCOPE=SPFILE;
alter system set AQ_TM_PROCESSES=0 scope=both;
show parameter job_queue_processes;
show parameter AQ_TM_PROCESSES;
Note：切换完成后开启
1.4：停掉业务（local=no）
Note：中断外部业务连接数据库的进程。防止写入数据。如果开启了dbconsole，停掉dbconsole
1.5 状态查询
set linesize 200;
set long 100000;
column database_role for a20;
column protection_mode for a21;
column protection_level for a18;
column open_mode for a20;
column switchover_status for a20;
select database_role,protection_mode,protection_level,open_mode,switchover_status from v$database;
Note：
如果switchover_status为TO_STANDBY说明可以直接转换
alter database commit to switchover to physical standby;
执行之后就会shutdown
如果switchover_status为SESSIONS ACTIVE 则关闭会话
alter database commit to switchover to physical standby with session shutdown
3：standby
3.1状态查询
set linesize 200;
set long 100000;
column database_role for a20;
column protection_mode for a21;
column protection_level for a18;
column open_mode for a20;
column switchover_status for a20;
select database_role,protection_mode,protection_level,open_mode,switchover_status from v$database;
SQL> column database_role for a20;
SQL> column protection_mode for a21;
SQL> column protection_level for a18;
SQL> column open_mode for a20;
SQL> column switchover_status for a20;
SQL> select database_role,protection_mode,protection_level,open_mode,switchover_status from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE SWITCHOVER_STATUS
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANC READ ONLY WITH APPLY SESSIONS ACTIVE
E
Note：若 SWITCHOVER_STATUS 为 not allowed 则表示当前备库为不可以做switch的操作
alter database commit to switchover to physical standby;
shutdown immediate;
select DATABASE_ROLE from v
d
a
t
a
b
a
s
e
;
s
e
l
e
c
t
O
P
E
N
M
O
D
E
,
P
R
O
T
E
C
T
I
O
N
M
O
D
E
,
P
R
O
T
E
C
T
I
O
N
L
E
V
E
L
,
S
W
I
T
C
H
O
V
E
R
S
T
A
T
U
S
f
r
o
m
v
database; select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v
database;selectOPENMODE,PROTECTIONMODE,PROTECTIONLEVEL,SWITCHOVERSTATUSfromvdatabase;
6：standby切换成primary
alter database commit to switchover to primary with session shutdown;
7：重启原备库新的primary
shutdown immediate;
startup;
状态查询
set linesize 200;
set long 100000;
column database_role for a20;
column protection_mode for a21;
column protection_level for a18;
column open_mode for a20;
column switchover_status for a20;
select database_role,protection_mode,protection_level,open_mode,switchover_status from v$database;
8：重启原主库新的standby
startup mount;
alter database open;
alter database recover managed standby database disconnect from session;
状态查询
set linesize 200;
set long 100000;
column database_role for a20;
column protection_mode for a21;
column protection_level for a18;
column open_mode for a20;
column switchover_status for a20;
select database_role,protection_mode,protection_level,open_mode,switchover_status from v$database;
#-------------------------------------------------------------------------------------------------------------
set linesize 200;
set long 100000;
column INSTANCE_NAME for a20;
column HOST_NAME for a20;
column STARTUP_TIME for a20;
column STATUS for a20;
column DATABASE_STATUS for a20;
SELECT INSTANCE_NAME, HOST_NAME, STARTUP_TIME, STATUS, DATABASE_STATUS FROM V$INSTANCE;
alter system switch logfile;
alter system archive log current;
select * from (select SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log order by 1 desc) where rownum<10;
column Sequence for a5;
column NAME for a40;
column Applied for a10;
select SEQUENCE#, NAME, Applied from v$archived_log order By SEQUENCE#;
recover managed standby database using current logfile disconnect from session;
set linesize 200;
set long 100000;
SELECT PROCESS, CLIENT_PROCESS,THREAD#, SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
查看进程
SELECT PROCESS, CLIENT_PROCESS,THREAD#, SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
正常主端
SQL> SELECT PROCESS, CLIENT_PROCESS,THREAD#, SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
PROCESS CLIENT_P THREAD# SEQUENCE# STATUS
ARCH ARCH 1 76 CLOSING
ARCH ARCH 1 68 CLOSING
ARCH ARCH 1 75 CLOSING
ARCH ARCH 1 72 CLOSING
LNS LNS 1 77 WRITING
正常备端
SQL> SELECT PROCESS, CLIENT_PROCESS,THREAD#, SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
PROCESS CLIENT_P THREAD# SEQUENCE# STATUS
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 1 75 CLOSING
ARCH ARCH 1 76 CLOSING
RFS N/A 0 0 IDLE
RFS LGWR 1 77 IDLE
MRP0 N/A 1 77 APPLYING_LOG
7 rows selected.
alter system switch logfile;
set linesize 200;
set long 100000;
column database_role for a20;
column protection_mode for a21;
column protection_level for a18;
column open_mode for a20;
column switchover_status for a20;
select database_role,protection_mode,protection_level,open_mode,switchover_status from v$database;
查看数据库角色和开启模式
正常主端
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE
正常备端
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY WITH APPLY
查看数据库角色和切换状态
主端
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
PRIMARY TO STANDBY
备端
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
PHYSICAL STANDBY NOT ALLOWED
select status,error from v$archive_dest;
create pfile=‘C:pfile.ora’ from spfile;
create pfile=’/home/oracle/pfile.ora’ from spfile;
alter system set log_archive_dest_state_1=enable scope=BOTH;
alter system set log_archive_dest_state_2=enable scope=BOTH;
https://blog.csdn.net/imliuqun123/article/details/77160516
以下是一些消除主备库之间GAP的命令和说明：
–主库，将所有未传送的redo传送给从库，target_db_name使用DB_UNIQUE_NAME 。
ALTER SYSTEM FLUSH REDO TO ‘target_db_name’;
eg:
ALTER SYSTEM FLUSH REDO TO ‘STANDBY’;
最后
以上就是寒冷钢笔为你收集整理的DG 排错笔记1.1检查ADG状态是否正常1.2 检查是否有gap1.3停掉job（primary）1.4：停掉业务（local=no）1.5 状态查询3：standby3.1状态查询状态查询状态查询的全部内容，希望文章能够帮你解决DG 排错笔记1.1检查ADG状态是否正常1.2 检查是否有gap1.3停掉job（primary）1.4：停掉业务（local=no）1.5 状态查询3：standby3.1状态查询状态查询状态查询所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复