adg日常操作手册

1、启用实时应用
alter database recover managed standby database using current logfile disconnect from session;
2、取消实时应用
alter database recover managed standby database cancel;
3、缺gap处理（此操作在有备份的情况下，在生产环境执行操作，可以使用默认的归档路径）
run{
set archivelog destination to ‘/racdb_arch’;
restore archivelog from sequence 49 thread 2;
}

注册方式：
ALTER DATABASE REGISTER LOGFILE ‘/physical_standby1/thread1_dest/arcr_1_7.arc’;
将确实的归档scp到不是归档目录的文件夹下：
catalog start with ‘’;
4、指定时间恢复归档
restore archivelog time between “to_date(‘2016-03-01 20:00:00’,‘yyyy-mm-dd hh24:mi:ss’)” and “to_date(‘2016-03-02 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)”;
5、如果主备incarnation不一致需要重新指定
list incarnation;
reset database to incarnation 1;
5、如果开启应用日志模式无法正常从归档日志恢复,需要执行这个指令应用归档日志等应用到最近的一个归档日志后再开启应用日志模式
alter database recover automatic standby database;
有时standby中断一段时间后起来，开启应用日志模式无法正常从归档日志恢复，需要执行这个指令应用归档日志等应用到最近的一个归档日志后再开启应用日志模式
6、生产中心检测通道是状态
set lines 200
col DESTINATION for a50
SELECT DEST_ID,DESTINATION, STATUS, ERROR FROM gV$ARCHIVE_DEST WHERE DEST_ID=2; set lines 200 col DESTINATION for a50 SELECT DEST_ID,DESTINATION, STATUS, ERROR FROM gV$ARCHIVE_DEST WHERE DEST_ID=3;
7、检查主备配置信息
set line 1000
col name format a25
col VALUE format a200
SELECT INST_ID,a.NAME,
a.VALUE
FROM gvparameter a WHERE a.name in ('dg_broker_start','db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_3','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3','log_archive_max_processes','remote_login_passwordfile','db_file_name_convert','log_file_name_convert','standby_file_management','fal_server','fal_client','dg_broker_config_file1','dg_broker_config_file2'); 8、主备检查角色 select switchover_status ,open_mode ,database_role from gvdatabase;
9、备库检查
查看备库ADG的MRP、RFS进程状态
select thread#, process, status, client_process, thread#,sequence# from vmanaged_standby; 10、查看备库ADG的数据同步状态 如果transport lag 和apply lag 值为0，说明日志传输和应用正常 如果transport lag 对应的value值不为0，说明日志发送存在GAP 如果apply lag对应的value值不为0，说明日志应用存在GAP COLUMN NAME FORMAT A24 COLUMN VALUE FORMAT A16 COLUMN DATUM_TIME FORMAT A24 SELECT NAME, VALUE,DATUM_TIME FROM VDATAGUARD_STATS;
11、备库检查gap
select thread#,low_sequence#,high_sequence# from varchive_gap; 12、七日归档总量，日平均量 select * from ( select trunc(FIRST_TIME,'dd') ARCH_DATE,trunc(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1204,2) GB from varchived_Log
where DEST_ID=1
and FIRST_TIME>trunc(sysdate-7,‘dd’)
and FIRST_TIME<trunc(sysdate,‘dd’)
group by trunc(FIRST_TIME,‘dd’)
) order by 1;

select trunc(sum(BLOCKSBLOCK_SIZE)/1024/1024/1204,2) Total_GB, trunc(sum(BLOCKSBLOCK_SIZE)/7/1024/1024/1204,2) Average_GB
from varchived_Log where DEST_ID=1 and FIRST_TIME>trunc(sysdate-7,'dd') and FIRST_TIME 14、日志传输状态
col DESTINATION for a50
SELECT DEST_ID,DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2; SELECT DEST_ID,DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=3;
14、数据库运行模式
col PROTECTION_MODE for a20
col SWITCHOVER_STATUS for a10
col PROTECTION_MODE for a10
col DATABASE_ROLE for a10
col OPEN_MODE for a10
col FORCE_LOGGING for a5
col DB_UNIQUE_NAME for a10
select DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,FORCE_LOGGING,switchover_status from vdatabase; 15、adg的配置 select * from VDATAGUARD_CONFIG;
16、ADG运行模式
select DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,FORCE_LOGGING,switchover_status from vdatabase; 17、ADG 同步时差 COLUMN NAME FORMAT A24 COLUMN VALUE FORMAT A16 COLUMN DATUM_TIME FORMAT A24 SELECT NAME, VALUE,DATUM_TIME FROM VDATAGUARD_STATS;
18、ADG服务运行状态
select thread#, process, status, client_process, sequence# from vmanaged_standby; 19、ADG 归档应用是否有gap select thread#,low_sequence#,high_sequence# from varchive_gap;
20、灾备环境创建spfile文件方式，必须是库在nomount，mount，或者open，如果是关闭状态会把软连接创建到DB_UNKNOWN
create spfile=’+datadg/drcbtsdb/PARAMETER/spfilecbtsdb.ora’ from pfile=’/home/oracle/pfile.ora’;
21、创建密码文件，处理密码问题ORA-46952，使用这种方式比较靠谱
pwcopy /home/grid/password +datadg/drcbtsdb/PASSWORD/password
pwcopy --dbuniquename /tmp/pw +DATAP//PASSWORD/pwd

可以使用下面的创建就没有问题
srvctl modify database -d drpaycode -pwfile
orapwd input_file=’/home/oracle/pwdpaycode.256.1035404803’ file=’+datadg/drpaycode/password/password’ dbuniquename=‘drpaycode’

foramt默认值是12.2 数据库自动创建的format为12
orapwd input_file=’/home/oracle/orapwcrmdb’ file=’/u01/app/oracle/product/19c/dbhome_1/dbs/orapwcrmdb’ dbuniquename=‘drcrmdb’ format=12

orapwd input_file=’/home/oracle/orapwcrmdb’ file=’/u01/app/oracle/product/19c/dbhome_1/dbs/orapwcrmdb’ format=12.2
由于password format版本不一致导致主备的mrp进程起不来处理
oracle12.2 adg ORA-46952: standby database format mismatch for password file

22、local_listener
select ‘alter system set ‘||p.name||’=’’’||trim(p.value)||’’’ scope=both sid=’’’||i.instance_name||’’’;’
from gv$parameter p,gv$instance i
where p.name=‘local_listener’
and p.INST_ID=i.INST_ID;

23、数据库机器异常关机导致归档写丢失
ERROR at line 1:
ORA-00742: Log read detects lost write in thread 1 sequence 213 block 12756
ORA-00312: online log 1 thread 1: ‘/oradata/CRMDB/redo01.log’

参数文件使用一下参数强制将库打开
_allow_resetlogs_corruption=‘TRUE’
_allow_error_simulation=TRUE

*.undo_management=‘MANUAL’

alter database datafile ‘/oradata/CRMDB/undotbs01.dbf’ offline drop ;

alter database open resetlogs;

create undo tablespace undo2 datafile ‘/oradata/CRMDB/undotbs02.dbf’ size 100m ;
drop tablespace UNDOTBS1 including contents and datafiles;

create undo tablespace UNDOTBS1 datafile ‘/oradata/CRMDB/undotbs01.dbf’ size 1G ;

drop tablespace undo2 including contents and datafiles;

*._offline_rollback_segment=(’_SYSSMU1_1261223759$','_SYSSMU2_27624015$’,’_SYSSMU3_2421748942$','_SYSSMU4_625702278$’,’_SYSSMU5_2101348960$','_SYSSMU6_813816332$’,’_SYSSMU7_2329891355$','_SYSSMU8_399776867$’,’_SYSSMU9_1692468413$','_SYSSMU10_930580995$’)
UNDOTBS1

_CORRUPTED_ROLLBACK_SEGMENTS =(’_SYSSMU1_1261223759$','_SYSSMU2_27624015$’,’_SYSSMU3_2421748942$','_SYSSMU4_625702278$’,’_SYSSMU5_2101348960$','_SYSSMU6_813816332$’,’_SYSSMU7_2329891355$','_SYSSMU8_399776867$’,’_SYSSMU9_1692468413$','_SYSSMU10_930580995$’)
————————————————
版权声明：本文为CSDN博主「hotye393」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/hotye393/article/details/6176120
一般这种情况需要重建undo解决一些报错

推荐scn的event
event=“21307096 trace name context forever, level 3”

24、ORA-16047: DGID mismatch between destination setting and target database
由于没有配置log_archive_config
重新配置
log_archive_config
25、adg，由于备库的目录空间满了，导致创建的数据文件没有成功
select name from v$database;
ALTER SYSTEM SET standby_file_management=‘MANUAL’
alter database create datafile ‘/home/db/oracle/product/19c/dbhome_1/dbs/UNNAMED00018’ as ‘+DATA’ size 30G;
ALTER SYSTEM SET standby_file_management=‘AUTO’
26、rac环境 db_file_name_convert，log_file_name_convert两参数是静态参数，需要重启库操作，但是主库一般没有停库窗口，有时候会导致，一个节点正常，一个节点重启导致参数文件不一致，因为静态参数是需要重启库生效
重启rac其中的一个节点，另外节点正常，如下报错：
ora-01105，ora-01677
我们尝试将set db_file_name_convert，log_file_name_convert为空，报错如下：
alter system set db_file_name_convert=’’ scope=spfile sid=’’;
alter system set log_file_name_convert=’’ scope=spfile sid=’’;

ORA-32017: failure in updating SPFILE
ORA-01678: parameter db_file_name_convert must be pairs of pattern and replacement strings

使用reset方式修改db_file_name_convert，log_file_name_convert，这种方式就是清空spfile文件的参数，下次重启库生效
alter system reset db_file_name_convert scope=spfile sid=’’;
alter system reset log_file_name_convert scope=spfile sid=’’;

27、ora-16191一般都是主库修改了sys密码，需要重新拷贝主库密码文件到备库

-------------------------主备切换--------------------
19c 主备switchover切换步骤：
在主库执行target_db_name 为db_uniquename
ALTER DATABASE SWITCHOVER TO drcrmdb VERIFY;在做switchover前执行检查校验步骤
ALTER DATABASE SWITCHOVER TO target_db_name [FORCE] [VERIFY];
ALTER DATABASE SWITCHOVER TO crmdb VERIFY;
主库执行操作：变为不看
ALTER DATABASE SWITCHOVER TO drcrmdb ;
startup ;
备库直接：变为主库
alter database open;
新主库：应用日志
alter database recover managed standby database using current logfile disconnect;

回切：
新主库执行操作：
ALTER DATABASE SWITCHOVER TO crmdb ;
新备库执行操作：变为主库
alter database open;
备库：原备库
执行操作
alter database recover managed standby database using current logfile disconnect;

主备切换步骤
备库检查gap
select thread#,low_sequence#,high_sequence# from v$archive_gap;

-------------------------------------------主备切换-------------------------------
主库切换备库操作
SQL>select switchover_status,open_mode from v$database; SQL>alter database commit to switchover to physical standby with session shutdown; SQL>startup; SQL>select switchover_status,open_mode from v$database;
备库切换至primary的状态
select switchover_status,open_mode from v$database; SQL> alter database commit to switchover to primary; SQL>select switchover_status,open_mode from v$database;
SQL>alter database open;
SQL>select switchover_status,open_mode from vdatabase; 备库操作应用日志 alter database recover managed standby database using current logfile disconnect; 监控mgr进程 #on Standby SET LINES 200 SET PAGESIZE 9999 COL CLIENT_PID FOR A12; select pid,process,status ,client_process,client_pid,thread#,sequence#,block#,blocks from vmanaged_standby;
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from VARCHIVED_LOG; 3.6.5.检测应用率和活动率 #on Standby SQL> SELECT to_char(start_time, 'DD-MON-RR HH24:MI:SS') start_time, item, sofar FROM vrecovery_progress
WHERE item IN (‘Active Apply Rate’,
‘Average Apply Rate’,
‘Redo Applied’)
/
检测重做传输与应用滞后
#on Standby
col name for a13;
col value for a20;
col unit for a30;
set lines 200;
SELECT NAME, VALUE, unit, time_computed
FROM v$dataguard_stats
WHERE NAME IN (‘transport lag’, ‘apply lag’);

V$DATAGUARD_PROCESS
---------------------------------------------------------------------------主备切换步骤----------------------