常用查询
环境检查
-- 查看数据库状态
select NAME,OPEN_MODE, PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
-- 查看实例信息
select INSTANCE_NAME,STATUS,LOGINS,INSTANCE_ROLE from v$instance;
-- 查看归档路径信息
select dest_name,status,error from v$archive_dest;
-- 按照线程分组查看已经应用的最大的日志序列号
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
-- 查看归档存放位置,所属线程、日志序列号,是否应用
select name,thread#,sequence#,applied from v$archived_log;
-- 查看有没有归档没有被应用,没有的话证明数据是同步的,没有丢任何数据(备库查询)
select name,thread#,sequence# from v$archived_log where applied='NO';
-- 查看日志第一次归档时间和下一次归档时间
select name,thread#,sequence#,applied,first_time,next_time from v$archived_log;
-- 查看归档是否有中断
select * from v$archive_gap;
主备切换
-- 环境检查
select NAME,OPEN_MODE, PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
/*主库switchover状态为 session active说明还有活动的会话
备库switchover状态为not allowed表示还不能切换*/
-- 备库查询(是否有未被应用的日志)
select THREAD#,SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log where APPLIED='NO';
-- 备库先取消恢复
alter database recover managed standby database cancel;
-- 主库操作(主库切备库)
alter database commit to switchover to physical standby;
or:
alter database commit to switchover to physical standby with session shutdown;
/*转换结束之后主库自动关闭*/
-- 启动主库
startup
-- 此时查询主库信息就会看到变为了备库
select NAME,OPEN_MODE, PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
-- 备库操作(备库切主库)
alter database commit to switchover to primary;
or:
alter database commit to switchover to primary with session shutdown;
切换报错:ORA-16139
则恢复一下,再取消恢复
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
然后再切换
alter database commit to switchover to primary;
or:
alter database commit to switchover to primary with session shutdown;
切换成功之后查看备库状态
select open_mode,database_role,switchover_status,protection_mode from v$database;
此时就可以看到备库变为了主库,同时备库的状态为mount
打开备库(新主库)
alter database open;
-- 新备库启动恢复
alter database recover managed standby database disconnect from session;
-- 查看归档序列是否一致
archive log list;
-- 新主库(原备库)测试:
conn /as sysdba;
create tablespace test_tbs2 datafile 'D:\oracle\oradata\dbsta\test2.dbf' size 100m autoextend off;
create user test_user2 identified by test default tablespace test_tbs2;
grant dba to test_user2;
create table test_user2.yk (id number);
insert into test_user2.yk values(666);
commit;
select * from test_user2.yk;
-- 主库切换归档之后备库才能查询到信息
alter system switch logfile;
-- 查看归档序列是否一致
archive log list;
-- 主备回切
-- 环境检查
select NAME,OPEN_MODE, PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
/*主库switchover状态为 session active说明还有活动的会话
备库switchover状态为not allowed表示还不能切换*/
-- 备库查询(是否有未被应用的日志)
select THREAD#,SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log where APPLIED='NO';
-- 备库先取消恢复
alter database recover managed standby database cancel;
-- 主库操作(主库切备库)
alter database commit to switchover to physical standby;
or:
alter database commit to switchover to physical standby with session shutdown;
/*转换结束之后主库自动关闭*/
-- 启动主库
startup
-- 此时查询主库信息就会看到变为了备库
select NAME,OPEN_MODE, PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
-- 备库操作(备库切主库)
alter database commit to switchover to primary;
or:
alter database commit to switchover to primary with session shutdown;
切换报错:ORA-16139
则恢复一下,再取消恢复
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
然后再切换
alter database commit to switchover to primary;
or:
alter database commit to switchover to primary with session shutdown;
切换成功之后查看备库状态
select open_mode,database_role,switchover_status,protection_mode from v$database;
此时就可以看到备库变为了主库,同时备库的状态为mount
打开备库(新主库)
alter database open;
--新备库启动恢复
alter database recover managed standby database disconnect from session;
--查看归档序列是否一致
archive log list;
--新主库(原备库)测试:
conn /as sysdba;
create tablespace test_tbs3 datafile 'D:\oracle\oradata\dbpri\test3.dbf' size 100m autoextend off;
create user test_user3 identified by test default tablespace test_tbs3;
grant dba to test_user3;
create table test_user3.yk (id number);
insert into test_user3.yk values(666);
commit;
select * from test_user3.yk;
--主库切换归档之后备库才能查询到信息
alter system switch logfile;
--查看归档序列是否一致
archive log list;
归档删除策略配置
#归档删除策略(在主库和备库配置归档删除策略)
#备库:
rman target sys/Q1w2e3r4@dgplatform
configure archivelog deletion policy to applied on standby;
#主库:
rman target /
configure archivelog deletion policy to applied on all standby;
配这个参数的意义:假设配置的归档删除策略是3天后删除归档,则3天后归档就会被删除,不配置这个参数,即便是归档没有应用到备库,归档也被删除了,这就导致主备数据不一致了,配置了这个参数之后,要删除归档前提是归档应用到了备库,这样才能删除
数据库启停操作
-- 关机
先关主库,再关备库
关闭备库前,做取消恢复操作
alter database recover managed standby database cancel;
关闭主库与备库的监听程序
lsnrctl stop
关闭OS
-- 开机
先开备库,再开主库
进去备库,
启动监听程序
lsnrctl start
启动备库数据库:
startup
选择一种恢复方式:
#归档式同步
alter database recover managed standby database disconnect from session;
#实时同步:
alter database recover managed standby database using current logfile disconnect from session;
然后启动主库
巡检命令
-- 检查归档序列信息
archive log list;
-- 当前时间与预计下次产生归档的时间的间隔
select 'standby gap is: ' || to_char(apply_lag) || ' minute at ' || to_char(sysdate, 'yyyy-mm-dd hh24:mi')||' DATABASE: testdb' as warn_message
from (select round((sysdate - MAX(G.NEXT_TIME))*1440) as apply_lag
from v$archived_log G
where G.applied = 'YES');
-- apply lag(备库应用延迟),transport lag(日志传输延迟)
select NAME,VALUE from v$dataguard_stats where regexp_like(name,'lag');
v$archived_log详解
V$ARCHIVED_LOG视图描述了系统中已经归档的日志文件的相关信息。归档日志是ARCHIVELOG模式的一种,用来记录DML以及DDL对数据库中对象所做的更改,保护数据库以及实施重做数据库恢复。
V$ARCHIVED_LOG视图的主要用途是查看已经归档的日志的信息,确定要恢复的日志。可以直接执行:
#查询当前被归档日志的相关信息
SELECT ARCHIVED, DELETED, SEQUENCE#, RESETLOGS_CHANGE#,
FLAGS, ARCHIVE_DATE, ARCHIVE_SCN, SESSION_INFO
FROM V$ARCHIVED_LOG;
V$ARCHIVED_LOG
displays archived log information from the control file, including archive log names.An archive log record is inserted after the online redo log is successfully archived or cleared (name column is
NULL
if the log was cleared). If the log is archived twice, there will be two archived log records with the sameTHREAD#
,SEQUENCE#
, andFIRST_CHANGE#
, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy and whenever a copy of a log is made with the RMANCOPY
command.
Column | Datatype | Description | 解释 |
---|---|---|---|
RECID | NUMBER | Archived log record ID | 归档日志记录的唯一标识号 |
STAMP | NUMBER | Archived log record stamp | 时间戳,与RECID 一起构成归档日志记录的全局唯一标识 |
NAME | VARCHAR2(513) | Archived log file name. If set to NULL , either the log file was cleared before it was archived or an RMAN backup command with the “delete input” option was executed to back up archivelog all (RMAN> backup archivelog all delete input; ). | 归档日志文件名 |
DEST_ID | NUMBER | Original destination from which the archive log was generated. The value is 0 if the destination identifier is not available. | 归档生成的目标 |
THREAD# | NUMBER | Redo thread number | 数据库线程的编号。Oracle数据库可以由多个线程组成,每个线程负责处理特定的事务。 |
SEQUENCE# | NUMBER | Redo log sequence number | 归档日志的序列号。表示日志的生成顺序。 |
RESETLOGS_CHANGE# | NUMBER | Resetlogs change number of the database when the log was written | 重置日志序列号,用于标识新的一组日志 |
RESETLOGS_TIME | DATE | Resetlogs time of the database when the log was written | 日志重置的时间戳编号 |
RESETLOGS_ID | NUMBER | Resetlogs identifier associated with the archived redo log | 重置日志的时间戳 |
FIRST_CHANGE# | NUMBER | First change number in the archived log | 归档日志中第一个重做记录的重做号(Redo Log Sequence Number) |
FIRST_TIME | DATE | Timestamp of the first change | 归档日志的创建时间 |
NEXT_CHANGE# | NUMBER | First change in the next log | 归档日志中最后一个重做记录的重做号 |
NEXT_TIME | DATE | Timestamp of the next change | 下一个归档日志的预计创建时间 |
BLOCKS | NUMBER | Size of the archived log (in blocks) | 归档日志的块数 |
BLOCK_SIZE | NUMBER | Redo log block size. This is the logical block size of the archived log, which is the same as the logical block size of the online log from which the archived log was copied. The online log logical block size is a platform-specific value that is not adjustable by the user. | 归档日志块的大小 |
CREATOR | VARCHAR2(7) | Creator of the archivelog:ARCH – Archiver processFGRD – Foreground processRMAN – Recovery ManagerSRMN – RMAN at standbyLGWR – Logwriter process | 触发归档的原因 |
REGISTRAR | VARCHAR2(7) | Registrar of the entry:RFS – Remote File Server processARCH – Archiver processFGRD – Foreground processRMAN – Recovery managerSRMN – RMAN at standbyLGWR – Logwriter process | 条目注册 |
STANDBY_DEST | VARCHAR2(3) | Indicates whether the entry is an archivelog destination (YES ) or not (NO ) | 显示条目是否是归档日志目标 |
ARCHIVED | VARCHAR2(3) | Indicates whether the online redo log was archived (YES ) or whether RMAN only inspected the log and created a record for future application of redo logs during recovery (NO ). | 显示是否归档(YES)或是否是RMAN恢复创建的记录(NO) |
APPLIED | VARCHAR2(9) | Indicates whether an archived redo log file has been applied to the corresponding physical standby database. The value is always NO for local destinations.This column is meaningful on a physical standby database for rows where REGISTRAR = RFS :If REGISTRAR = RFS and APPLIED = NO , then the log file has been received but has not yet been applied.If REGISTRAR = RFS and APPLIED = IN-MEMORY , then the log file has been applied in memory, but the data files have not yet been updated.If REGISTRAR = RFS and APPLIED = YES , then the log file has been applied and the data files have been updated.This column can be used to identify log files that can be backed up and deleted. When used for this purpose, the value IN-MEMORY should be treated as if it were NO . | 显示归档日志已被应用到了physical standby中,对本地节点值为NO在物理standby中的意义: REGISTRAR=RFS && APPLIED=NO,表示logfile已被接受并注册但没有应用 REGISTRAR=RFS && APPLIED=IN-MEMORY,表示在内存中已应用logfile,还没有更新到datafiles中 REGISTRAR=RFS && APPLIED=YES,表示已应用logfile并更新到datafiles |
DELETED | VARCHAR2(3) | Indicates whether an RMAN DELETE command has physically deleted the archived log file from disk, as well as logically removing it from the control file of the target database and from the recovery catalog (YES ) or not (NO ) | 显示是否使用了RMAN DELETE命令物理删除了归档日志,是否在逻辑上从controlfile恢复目录移除(YES/NO) |
STATUS | VARCHAR2(1) | Status of the archived log:A – AvailableD – DeletedU – UnavailableX – Expired | 归档日志状态 |
COMPLETION_TIME | DATE | Time when the archiving completed | 归档完成时间 |
DICTIONARY_BEGIN | VARCHAR2(3) | Indicates whether the log contains the start of a LogMiner dictionary (YES ) or not (NO ) | 显示是否是LogMiner数据字典包含日志的开始(YES/NO) |
DICTIONARY_END | VARCHAR2(3) | Indicates whether the log contains the end of a LogMiner dictionary (YES ) or not (NO ) | 显示是否是LogMiner数据字典包含日志的结束(YES/NO) |
END_OF_REDO | VARCHAR2(3) | Indicates whether the archived redo log contains the end of all redo information from the primary database (YES ) or not (NO ) | 显示primary端归档日志是否包含redo结束信息 |
BACKUP_COUNT | NUMBER | Indicates the number of times this file has been backed up. Values range from 0-15. If the file has been backed up more than 15 times, the value remains 15. | 显示文件被备份的次数(0-15),如果次数超出15,则保留显示为15 |
ARCHIVAL_THREAD# | NUMBER | Redo thread number of the instance that performed the archival operation. This column differs from the THREAD# column only when a closed thread is archived by another instance. | 执行归档操作的实例线程号,当关闭的线程被其他实例归档时值与THREAD#不同 |
ACTIVATION# | NUMBER | Number assigned to the database instantiation | 被实例分配的次数 |
IS_RECOVERY_DEST_FILE | VARCHAR2(3) | Indicates whether the file was created in the fast recovery area (YES ) or not (NO ) | 显示文件是否被创建到fast recovery ares(YES/NO) |
COMPRESSED | VARCHAR2(3) | Reserved for internal use | 保留作内部使用 |
FAL | VARCHAR2(3) | Indicates whether the archive log was generated as the result of a FAL request (YES ) or not (NO ) | 显示归档日志是否为FAL请求产生的(YES/NO) |
END_OF_REDO_TYPE | VARCHAR2(10) | Possible values are as follows:SWITCHOVER – Shows archived redo log files that are produced at the end of a switchoverTERMINAL – Shows archived redo log files produced after a failoverRESETLOGS – Shows online redo log files archived on the primary database after an ALTER DATABASE OPEN RESETLOGS statement is issuedACTIVATION – Shows any log files archived on a physical standby database after an ALTER DATABASE ACTIVATE STANDBY DATABASE statement is issuedempty string – Any empty string implies that the log is just a normal archival and was not archived due to any of the other events | SWITCHOVER :Switchover产生的EOR TERMINAL : Failover产生的EOR RESETLOGS : 主库执行ALTER DATABASE OPEN RESETLOGS语句后产生的归档 ACTIVATION :物理standby执行ALTER DATABASE ACTIVATE STANDBY DATABASE语句后产生的归档 空值 :正常的归档 |
BACKED_BY_VSS | VARCHAR2(3) | Whether or not the file has been backed up by Volume Shadow Copy Service (VSS). This column is reserved for internal use. | 显示文件是否以Volume Shadow Copy Service(VSS)备份,保留作内部使用 |
CON_ID | NUMBER | The ID of the container to which the data pertains. Possible values include:0 : This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.1 : This value is used for rows containing data that pertain to only the rootn: Where n is the applicable container ID for the rows containing data | 容器ID,对于多租户数据库,用于标识租户的容器 |
问题记录
版本bug
DataGuard利用backup incremental from scn解决gap,11.2.0.4 v$archive_gap不显示存在的gap
https://blog.csdn.net/jwocnimabi/article/details/103200164
DataGuard ORA-01111 问题处理
https://blog.51cto.com/dbadadong/5196056
https://blog.csdn.net/sandy9919/article/details/84956266
参考资料
https://www.modb.pro/db/42408