wukaiqiang 发布的文章

指定数据目录信息,docker环境在容器外备份需要。

vim /soft/mysql.cnf
[mysqld]
datadir=/root/.data/mysql/data
log-bin=/root/.data/mysql/data/mysql-bin
log-bin-index=/root/.data/mysql/data/mysql-bin.index

备份

./xtrabackup --defaults-file=/soft/mysql.cnf --host=10.12.6.229 --user=root --password='MGR_12345678' --port=3308 --backup --target-dir /soft/full-database-20221017

转移

scp -r /soft/full-database-20221017 root@10.12.6.233:/soft/

从库还原

./xtrabackup --prepare --target-dir=/soft/full-database-20221017
docker stop mysql-mgr-m
rm -rf /root/.data/mysql/data/*
rsync -avrP /soft/full-database-20221017/ --exclude='xtrabackup_*' /root/.data/mysql/data/ > /soft/rsync.log
docker start mysql-mgr-m

change master to
master_host='10.12.7.173',
master_user='group',
master_password='group_123456',
master_port=3307,
master_auto_position=1;

start slave;

生产案例二(重建master):

指定数据目录信息,docker环境在容器外备份需要。

vim /soft/mysql.cnf
[mysqld]
datadir=/root/.data/mysql/data
log-bin=/root/.data/mysql/data/mysql-bin
log-bin-index=/root/.data/mysql/data/mysql-bin.index

备份

./xtrabackup --defaults-file=/soft/mysql.cnf --host=10.12.6.229 --user=root --password='MGR_12345678' --port=3308 --backup --target-dir /soft/full-database-20221017

转移

scp -r /soft/full-database-20221017 root@10.12.6.233:/soft/

从库还原

./xtrabackup --prepare --target-dir=/soft/full-database-20221017
docker stop mysql-mgr-m
rm -rf /root/.data/mysql/data/*
rsync -avrP /soft/full-database-20221017/ --exclude='xtrabackup_*' /root/.data/mysql/data/ > /soft/rsync.log
docker start mysql-mgr-m

查看最后一条GTID事物

cat /soft/full-database-20221017/xtrabackup_binlog_info

mysql-bin.000515 224 aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-182146236:182880967-182916144

进入数据开启组复制

docker exec -it mysql-mgr-m /bin/bash

mysql -uroot -p'xxxxxxxx'

stop group_replication;
SET SQL_LOG_BIN=0;
reset master;
set @@GLOBAL.GTID_PURGED= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-182146236:182880967-182916144';
SET SQL_LOG_BIN=0;
start group_replication;

检查

select * from performance_schema.replication_group_members;

SQL> alter session set workarea_size_policy=manual; --执行后可以手动调整排序区而不是自动使用pga

Session altered

SQL> alter session set sort_area_size=1073741824; --提高当前会话的排序区

Session altered

SQL> alter session set sort_area_retained_size=1073741824; --排序结束后用于返回排序行。

Session altered

SQL> alter session set db_file_multiblock_read_count=128; --增加一次读取的block的数量.

Session altered

SQL> set timing on;
SQL> set serveroutput on;

SQL> create index IDX_PRWAY_BILLID_update_time on TMS_WAY_BILL (update_time) nologging online parallel 4
2 tablespace IDEAS_DATA
3 pctfree 10
4 initrans 10
5 maxtrans 255
6 storage
7 (
8 initial 64K
9 next 1M
10 minextents 1
11 maxextents unlimited
12 ) ;

Index created

Executed in 1435.04 seconds

SQL> alter index IDX_PRWAY_BILLID_update_time noparallel ;

Index altered

Executed in 0.637 seconds

监控索引使用情况:
alter index IX_USERINFO$SALARY monitoring usage ;
alter index IX_USERINFO$SALARY nomonitoring usage ;
select * from v$object_usage;

--查监控历史
select u.name owner,

   io.name index_name,
   t.name table_name,
   decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
   decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
   ou.start_monitoring start_monitoring,
   ou.end_monitoring end_monitoring

from sys.user$ u,

   sys.obj$         io,
   sys.obj$         t,
   sys.ind$         i,
   sys.object_usage ou

where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#
order by 6 desc

Oracle 如何停止正在后台执行的impdp/expdp 任务
Oracle 在执行 impdp或expdp过程中如果不想执行按Ctrl+C中断,但进程并未中断仍在后台运行,可以看导出的文件大小一直在长
expdp正确停止过程:

  1. 查看正在运行的job,可以发现自己的job还在执行
    select * from dba_datapump_jobs;
  2. 根据上面 job_name 进入到刚才执行的expdp下
    expdp system/123456 attach=SYS_EXPORT_TABLE_01
  3. 执行
    stop_job=immediate

    kill_job

附:
status:查看作业状态,监控作业进度

4.再次查看job为 not running,
select * from dba_datapump_jobs;


 

1、监控脚本(注:这里没用Sendmail工具发送邮件,如果用的话需要修改)

$cat check_oracle_dg_delay.sh 

#!/bin/sh
source ~/.bash_profile
#定义变量
v_datetime=`date +"%Y-%m-%d %H:%M:%S"`
v_username='oracle'
v_hostname=`hostname`
#获取IP地址
v_ip=`/sbin/ifconfig bond0|grep 'inet addr'|awk '{print $2}'|cut -d: -f2`
#发送人
v_sendmail='xxxxx@139.com'
#接收人,多个联系人用空格分开
v_receivemail='XXXXX@139.com XXXXX@163.com'
#路径
v_path="/home/${v_username}/scripts"
#####define tns#####
v_dbname='XXXXX'
v_standby_tns='xxxxx_dg'
#####报警阀值设置(单位:秒)#####
v_delay_time_warning=1800

#####Oracle DG 延时获取开始#####
sqlplus -S "/ as sysdba" <<EOF
--#####primary database####
col delay_time for 9999999999;
col tmp_delay_time new_val tmp_delay_time
set timing off time off verify off heading off trimout on trimspool on linesize 180;

spool ${v_path}/delay_time.log
select (to_date(to_char(scn_to_timestamp(current_scn),
                        'yyyy-mm-dd hh24:mi:ss'),
                'yyyy-mm-dd hh24:mi:ss') -
       to_date(to_char(scn_to_timestamp
                        ((select current_scn
                           from v\$database@phystandby.localdomain)),
                        'yyyy-mm-dd hh24:mi:ss'),
                'yyyy-mm-dd hh24:mi:ss')) * 24 * 3600 tmp_delay_time  
from v\$database;
spool off;

spool ${v_path}/delay_scn_and_time.log
col standby_current_scn for 999999999999999;
col primary_current_scn for 999999999999999;
col standby_current_time for a30;
col primary_current_time for a30;
col delay_time for 9999999999;
set heading on 
select current_scn primary_current_scn,
       (select current_scn from v\$database@phystandby.localdomain) standby_current_scn,
       to_char(scn_to_timestamp(current_scn), 'yyyy-mm-dd hh24:mi:ss') primary_current_time,
       to_char(scn_to_timestamp
               ((select current_scn from v\$database@phystandby.localdomain)),
               'yyyy-mm-dd hh24:mi:ss') standby_current_time,
       (to_date(to_char(scn_to_timestamp(current_scn),
                        'yyyy-mm-dd hh24:mi:ss'),
                'yyyy-mm-dd hh24:mi:ss') -
       to_date(to_char(scn_to_timestamp
                        ((select current_scn
                           from v\$database@phystandby.localdomain)),
                        'yyyy-mm-dd hh24:mi:ss'),
                'yyyy-mm-dd hh24:mi:ss')) * 24 * 3600 "delay_time(s)"
  from v\$database;
spool off;

exit
EOF
#####Oracle DG 延时获取结束#####

#####Oracle DG 延时时长提取#####
v_delay_time=`cat ${v_path}/delay_time.log|sed '/^$/d'|sed -e 's/[[:space:]][[:space:]]*/ /g'`
#####监控代码开始#####
if [ ${v_delay_time} -ge ${v_delay_time_warning} ] ; then

echo "############################" > ${v_path}/oracle_dg_delay.log
echo "USER: ${v_username}" >> ${v_path}/oracle_dg_delay.log
echo "HOSTNAME: ${v_hostname}" >> ${v_path}/oracle_dg_delay.log
echo "IP: ${v_ip}" >> ${v_path}/oracle_dg_delay.log
echo "############################" >> ${v_path}/oracle_dg_delay.log
cat ${v_path}/delay_scn_and_time.log >> ${v_path}/oracle_dg_delay.log
${v_path}/bsmtp -f ${v_sendmail} -h smtp.api.localdomain -s "${v_dbname} ORACLE DG DELAY ${v_delay_time}s WARNING!!! - ${v_datetime}" ${v_receivemail} < ${v_path}/oracle_dg_delay.log
fi
#####监控代码结束#####



2、配置crontab,每十分钟执行一次

$crontab -l

复制 
#oracle DG delay check
*/10 * * * * /home/oracle/scripts/check_oracle_dg_delay.sh >/dev/null 2>&1