oracle主备库查询,oracle dataguard 主备切换查看

14.查询备库状态

SQL> set linesize 150;

column DBNAME format a6;

column DBUNAME format a10;

column cftype format a8;

column OPEN_MODE format a20;

column DATABASE_ROLE format a18;

select name dbname,db_unique_name dbuname,controlfile_type cftype,database_role,open_mode from v$database;

15.备库启动日志应用

alter database recover managed standby database disconnect from session;

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

16.注册instance到集群中：

srvctl add database -d yxjc -n yxdb -o $ORACLE_HOME -m [] -p spfile_location -r physical_standby -a []

srvctl add instance -d yxjc -i yxdb1 -n yxdb1

srvctl add instance -d yxjc -i yxdb2 -n yxdb2

srvctl config database -d yxjchttps://www.cndba.cn/redhat/article/2511https://www.cndba.cn/redhat/article/2511https://www.cndba.cn/redhat/article/2511

17.停止日志应用的命令是：

alter database recover managed standby database cancel;

srvctl stop database -d yxjc

srvctl start database -d yxjc

18.备库启动日志应用

alter database recover managed standby database disconnect from session;

alter database recover managed standby database using current logfile disconnect from session;https://www.cndba.cn/redhat/article/2511

查看日志应用情况：

set pagesize 100

select sequence#,applied from v$archived_log order by 1;https://www.cndba.cn/redhat/article/2511

SEQUENCE# APPLIED

https://www.cndba.cn/redhat/article/2511

8 YES

9 YES

10 YES

如上，如果发现有个NO的，也是正常的，说明该日志在主库上还没有归档，可以在主库上运行alter system switch logfile;命令来进行日志切换，再到备库查看日志应用情况https://www.cndba.cn/redhat/article/2511

https://www.cndba.cn/redhat/article/2511

分别查看主库和备库的归档序列号是否一致：

先在主库手动切换一下日志：

alter system switch logfile;

System altered.

然后查看主库：

archive log list;

备库：

archive log list;

查看归档日志序列号是否一致。https://www.cndba.cn/redhat/article/2511

主库查询：

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

RECOVERY_MODE

MANAGED REAL TIME APPLY

备库查询：

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=1;

RECOVERY_MODE

MANAGED REAL TIME APPLYs

查看备库状态

SQL> set linesize 150;

column DBNAME format a6;

column DBUNAME format a10;

column cftype format a8;

column OPEN_MODE format a20;

column DATABASE_ROLE format a18;

select name dbname,db_unique_name dbuname,controlfile_type cftype,open_mode,database_role from v$database;

DBNAME DBUNAME CFTYPE OPEN_MODE DATABASE_ROLE

PCDB standby STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY

查询备库恢复管理进程情况

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

备库查询重做数据传输和应用滞后

SQL> set linesize 150;

set pagesize 20;

column name format a13;

column value format a20;

column unit format a30;

column TIME_COMPUTED format a30;

select name,value,unit,time_computed from v$dataguard_stats where name in (‘transport lag’,’apply lag’);

物理dataguard的switchover切换工作

select sequence#,applied from v$archived_log; (查询主备库日志序列是否一致)

1.查询主库的切换状态

Select switchover_status from v$database;

2.在主库上初始化切换到备库的操作,主库的切换操作会传输到备库，备库的状态会自动转换为to primary

alter database commit to switchover to physical standby with session shutdown wait;

3.关闭数据库，启动到mount 状态。

Shutdown immediate;

Startup mount;

Select database_role,switchover_status from v$database;

此时PRI主库的角色和状态已经发生转变。

4.在新的主库(原有的备库)启动日志应用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT disconnect from session;

5.验证备库的状态

Select database_role,switchover_status from v$database;

6.切换备库为主库(完成角色切换)

Alter database commit to switchover to primary with session shutdown wait;

7.查询备库是否完成角色切换

select database_role,switchover_status from v$database;

8.alter database open；

版权声明：本文为博主原创文章，未经博主允许不得转载。