oracle2

原创 yczloveyy 2023-08-28

568

NO.

行动事项

执行环境

手动操作步骤

脚本操作步骤

完成情况

责任团队

执行人

备注

计划开始时间

计划结束时间

配置监控【每小时巡检一次】、一级响应：
检查undo(500M)、长事务情况、长时间运行的sql、DG同步、GG lag、FRA(合福处理,性能指标,自动处理, 迁移后的第一个周一无问题就停)

OLD_PRD

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
COLUMN OPNAME FORMAT A30;
COLUMN MESSAGE FORMAT A40;
COLUMN START_T FORMAT A20;
COLUMN USERNAME FORMAT A10;
COLUMN MINUTES_REMAINING FORMAT 999999.9;
column kill for a20;
SELECT ''''||S.SID||','||S.SERIAL#||',@'||s.INST_ID||'''' kill, S.EVENT,MESSAGE,L.SQL_ID,
L.USERNAME,/*L.OPNAME,*/TO_CHAR(L.START_TIME,'YYYY-MM-DD HH24:MI:SS') START_T,/*TO_CHAR(L.LAST_UPDATE_TIME,'YYYY-MM-DD HH24:MI:SS') UPDATE_T,*/
ROUND(L.TIME_REMAINING/60,1) MINUTES_REMAINING,ROUND(SOFAR/TOTALWORK*100,1) COMPLETE_RATE,S.LOGON_TIME FROM GV$SESSION_LONGOPS L, GV$SESSION S
WHERE s.INST_ID=l.INST_ID and S.SID=L.SID AND L.SERIAL#=S.SERIAL# AND L.TIME_REMAINING>0 ORDER BY TIME_REMAINING ASC NULLS FIRST;

/paic/tmp/nasshare3/licw/gbs
./s.sh   ./sgm.sh ./swgq.sh
选择. chk_db

1 .检查是否有long running的sql
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
COLUMN OPNAME FORMAT A30;
COLUMN MESSAGE FORMAT A40;
COLUMN START_T FORMAT A20;
COLUMN USERNAME FORMAT A10;
COLUMN MINUTES_REMAINING FORMAT 999999.9;
column kill for a20;
SELECT ''''||S.SID||','||S.SERIAL#||',@'||s.INST_ID||'''' kill, S.EVENT,MESSAGE,L.SQL_ID,
L.USERNAME,/*L.OPNAME,*/TO_CHAR(L.START_TIME,'YYYY-MM-DD HH24:MI:SS') START_T,/*TO_CHAR(L.LAST_UPDATE_TIME,'YYYY-MM-DD HH24:MI:SS') UPDATE_T,*/
ROUND(L.TIME_REMAINING/60,1) MINUTES_REMAINING,ROUND(SOFAR/TOTALWORK*100,1) COMPLETE_RATE,S.LOGON_TIME FROM GV$SESSION_LONGOPS L, GV$SESSION S
WHERE s.INST_ID=l.INST_ID and S.SID=L.SID AND L.SERIAL#=S.SERIAL# AND L.TIME_REMAINING>0 ORDER BY TIME_REMAINING ASC NULLS FIRST;
2.检查临时表空间中临时段的 extents 数量
select * from (SELECT se.username, se.SID, se.serial#, se.machine, se.program, su.TABLESPACE,su.segtype, su.BLOCKS from
v$session se, v$sort_usage su WHERE se.saddr = su.session_addr
order by blocks desc) where rownum<5;

3 .检查数据库中是否有占用较多回滚段的事务：
SELECT * FROM (SELECT s.STATUS sess_status, T.STATUS,S.INST_ID,S.SID,S.SQL_ID,s.SERIAL#, S.USERNAME,S.MACHINE,T.START_TIME, S.LOGON_TIME,T.USED_UREC RELATED_ROWS, ROUND(T.USED_UBLK*BLOCK_SIZE/1024/1024,2) UNDO_SIZE_M
     FROM GV$SESSION S, GV$TRANSACTION T, DBA_TABLESPACES DT,GV$SYSTEM_PARAMETER P
     WHERE UPPER(P.NAME)='UNDO_TABLESPACE' AND DT.TABLESPACE_NAME=P.VALUE AND
     T.INST_ID=S.INST_ID AND
     P.INST_ID=S.INST_ID AND
     S.SADDR = T.SES_ADDR
     ORDER BY T.USED_UBLK DESC) WHERE ROWNUM<5;

e.检查DG同步
select dest_id, thread#,sequence#, to_char(first_time, 'yyyy-mm-dd hh24:mi:ss') last_apply_log_first_time,
       round((sysdate - first_time) * 24 * 60, 1) apply_lag_min
from v$archived_log
where recid = (select max(recid) from v$archived_log where applied = 'YES') ;

f.检查GG LAG

DBA

李成伟

巡检的发现的问题，一级响应，专人分析排查，风险及时上报

关注批处理任务完成情况，如有异常及时跟进处理

OLD_PRD

select * from (select round((sysdate - a.this_date) * 24 * 60, 2) elapsed_min,
a.sid, b.serial#,a.job, a.this_date, b.username From dba_jobs_running a, v$session b
where a.sid = b.sid and a.this_date is not null order by 1 desc ) where rownum<5

cd /paic/tmp/nasshare3/licw/gbs
./s.sh 选择. check_running_jobs 14

DBA

李成伟

未完成的批处理任务，当天18:00前必须都完成，11:00给出最终的评估结论

迁移当天增备(GLDG、GMDG、WGQDG)

OLD_ENV

/paic/tmp/nasshare3/licw/gbs
./s.sh   ./sgm.sh ./swgq.sh
选择 xtts_incr_backup      5
检查进度: check_xtts_incr_backup       6
确认增备完成后
选择 xtts_incr_update_from_scn 以更新下次增备的起点

DBA

李成伟

迁移当天第应用增备恢复（GLDG、GMDG、WGQDG）

NEW_PRD

cp /paic/tmp/nasshare3/licw/gbssource/xtts/tmpdir/tsbkupmap.txt /paic/tmp/nasshare/licw/gbs/target/tmpdir/tsbkupmap.txt
cp /paic/tmp/nasshare3/licw/gbssource/xtts/incr/* /paic/tmp/nasshare/licw/gbs/source/xtts/incr

cd   /paic/tmp/nasshare/licw/gbs
./t.sh ./tgm.sh ./twgq.sh
选择: apply_incr_backup      3
检查: check_apply_incr_backup       4
apply_incr.sh     ---已创建
nohup sh apply_incr.sh >apply_incr.log 2>&1 &
/bin/rm -rf /paic/tmp/nasshare/licw/gbs/target/tmpdir/FAILED
/bin/rm -rf /paic/tmp/nasshare3/licw/gbssource/xtts/incr/*
/bin/rm -rf /paic/tmp/nasshare/licw/gbs/source/xtts/incr/*

DBA

李成伟

循环以上两个步骤

OLD_ENV/NEW_ENV

升级前的最后一次增备，根据当天的增备平均时长和维护开始时间确定，确保在维护开始后增备能在最短时间内完成。

DBA

李成伟

新库环境检查

NEWPRD
NEWGMDG
NEWWGQDG

1. 以单节点启动
2. DG同步是否正常

DBA

刘用

通知备份组停止当天的备份任务

OLD_GM_DG

15号晚上开始停掉增备【包含日志备份】，注意检查同城FRA

存储备份组

李成伟

通知主机组同事临时停dpcow,miscow 停刷新cron

OLD_GM_DG

通知主机组同事临时停dpcow,miscow 刷新cron

DBA~~、主机组~~

~~李成伟~~

导出SPM(并行)

OLD_PRD

手动查导出了多少个
select count(distinct sql_handle||plan_name) ||' SPM WAS EXPORTED' from dba_sql_plan_baselines where accepted='YES' and enabled='YES'; ---48795
MIGUPS.GBS_SPM_2022

cd /paic/tmp/nasshare3/licw/gbs
./s.sh
选择 export_spm 9
--检查
选择 check_export_spm 11

DBA

李成伟

导出统计信息(并行)

OLD_PRD

select count(1)||' STATISTIC WAS EXPORTED;' from MIGUPS.GBS_STATS_2022;
MIGUPS.GBS_STATS_2022 -- 692533

cd /paic/tmp/nasshare3/licw/gbs
./s.sh
选择 export_stat 8
--检查选择 check_export_stat 10

30分钟

DBA

李成伟

保存源库对象信息以及性能相关的表(会被一同迁移到主库)

OLD_PRD

DROP TABLE MIGUPS.S_DBA_DATA_FILES;
DROP TABLE MIGUPS.S_DBA_DB_LINKS;
DROP TABLE MIGUPS.S_DBA_OBJECTS;
DROP TABLE MIGUPS.S_DBA_PROFILES;
DROP TABLE MIGUPS.S_DBA_ROLE_PRIVS;
DROP TABLE MIGUPS.S_DBA_ROLES;
DROP TABLE MIGUPS.S_DBA_SYS_PRIVS;
DROP TABLE MIGUPS.S_DBA_TAB_PRIVS;
DROP TABLE MIGUPS.S_DBA_TAB_STATISTICS;
DROP TABLE MIGUPS.S_DBA_TS_QUOTAS;
DROP TABLE MIGUPS.S_DBA_USERS;
DROP TABLE MIGUPS.SSQL;
DROP TABLE MIGUPS.SDBA_HIST_SQLSTAT;
DROP TABLE MIGUPS.SACTIVE_SESSION_HISTORY;
DROP TABLE MIGUPS.SDBA_HIST_ACTIVE_SESS_HISTORY;
DROP TABLE MIGUPS.SDBA_HIST_SNAPSHOT;
DROP TABLE MIGUPS.SSQLPLAN;
DROP table MIGUPS.s_dba_constraints;

CREATE TABLE MIGUPS.S_DBA_DATA_FILES   AS SELECT * FROM DBA_DATA_FILES;
CREATE TABLE MIGUPS.S_DBA_DB_LINKS   AS SELECT * FROM DBA_DB_LINKS;
CREATE TABLE MIGUPS.S_DBA_OBJECTS   AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE MIGUPS.S_DBA_PROFILES   AS SELECT * FROM DBA_PROFILES;
CREATE TABLE MIGUPS.S_DBA_ROLE_PRIVS   AS SELECT * FROM DBA_ROLE_PRIVS;
CREATE TABLE MIGUPS.S_DBA_ROLES   AS SELECT * FROM DBA_ROLES;
CREATE TABLE MIGUPS.S_DBA_SYS_PRIVS   AS SELECT * FROM DBA_SYS_PRIVS;
CREATE TABLE MIGUPS.S_DBA_TAB_PRIVS   AS SELECT * FROM DBA_TAB_PRIVS;
CREATE TABLE MIGUPS.S_DBA_TAB_STATISTICS   AS SELECT * FROM DBA_TAB_STATISTICS;
CREATE TABLE MIGUPS.S_DBA_TS_QUOTAS   AS SELECT * FROM DBA_TS_QUOTAS;
CREATE TABLE MIGUPS.S_DBA_USERS   AS SELECT * FROM DBA_USERS;
CREATE TABLE MIGUPS.s_dba_constraints as select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION_VC,R_OWNER,R_CONSTRAINT_NAME,DELETE_RULE,STATUS,DEFERRABLE,DEFERRED,VALIDATED,GENERATED,bad,rely,last_change,INDEX_OWNER,INDEX_NAME,INVALID,VIEW_RELATED,ORIGIN_CON_ID
from dba_constraints;

CREATE TABLE MIGUPS.SSQL AS SELECT * FROM V$SQL;
CREATE TABLE MIGUPS.SDBA_HIST_SQLSTAT AS SELECT * from DBA_HIST_SQLSTAT;
CREATE TABLE MIGUPS.SACTIVE_SESSION_HISTORY AS SELECT * from V$ACTIVE_SESSION_HISTORY;
CREATE TABLE MIGUPS.SDBA_HIST_ACTIVE_SESS_HISTORY AS SELECT * from DBA_HIST_ACTIVE_SESS_HISTORY;
CREATE TABLE MIGUPS.SDBA_HIST_SNAPSHOT AS SELECT * from DBA_HIST_SNAPSHOT;
CREATE TABLE MIGUPS.SSQLPLAN AS SELECT * from v$sql_plan;
create index migups.in_S_DBA_TAB_PRIVS on migups.S_DBA_TAB_PRIVS(owner, table_name, GRANTEE,PRIVILEGE);

cd /paic/tmp/nasshare3/licw/gbs
./s.sh
选择 create_migrate_table 12
检查 check_migrate_table 13
sqlplus /nolog <<eof>> create.sql.txt
conn /as sysdba
set sqlblanklines on
set trimout on;
set timing on;
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select sysdate from dual;
create table migups.ssql as select * from V\$sql;
create table migups.sDba_Hist_Sqlstat as select * from Dba_Hist_Sqlstat;
create table migups.sactive_session_history as select * from v\$active_session_history;
create table migups.sDba_Hist_Active_Sess_History as select * from Dba_Hist_Active_Sess_History;
create table migups.sDba_Hist_Snapshot as select * from Dba_Hist_Snapshot;
create table migups.ssqlplan as select * from v$sql_plan;
exit
eof

60分钟

DBA

李成伟

新库迁移前检查(迁移检查)

NEW_PRD

set lines 120
set pages 999
col "name" form a30
col "value" form a30
select name,value from v$parameter p where p.NAME in ('open_links','audit_trail','db_files');
结果为audit_trail=NONE db_files>8000 open_links建议为0 但是迁移需要依赖db_links，所以没调整。
alter system set audit_trail=none scope=spfile;（重启生效)

cd /paic/tmp/nasshare3/licw/gbs
./t.sh
选择 chk_db 7
会查1. BCT是否开启
a.检查数据库中的等待
select s.sid, s.serial#, s.username, s.machine,s.program,sw.event
from v$session_wait sw, v$session s where sw.sid=s.sid and sw.event not like 'SQL%' and sw.event not like 'rdbms%' and sw.event not like '%mon timer';
b.检查是否有long running的sql
select sid, target,opname, sofar,totalwork,time_remaining from v$session_longops where time_remaining>0;
c.检查临时表空间中临时段的 extents 数量
select tablespace_name, total_extents from v$sort_segment;
d.检查数据库中是否有占用较多回滚段的事务：
select xidusn,s.sid,s.last_call_et, s.username, used_ublk,USED_UREC,space,RECURSIVE, NOUNDO, START_UBAFIL,start_ubablk, start_uext from v$transaction t, v$session s where t.ses_addr=s.saddr;

DBA

李成伟

目标库新建PROFILE、ROLE、USERS

NEW_PRD

role.sql
-------------------------------------------
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
select case PASSWORD_REQUIRED
         when 'YES' then
          'create role ' || role || ' identified by values '''||du.password||''';'
         else
          'create role ' || role || ';'
       end
from dba_roles@DBLINK_MIGUP dr, sys.user$@DBLINK_MIGUP du
where du.name= dr.role   and dr.ROLE not in (select role from dba_roles);
-------------------------------------------
profile.sql 执行结果前先创建函数pa_pw_verify_function
CREATE OR REPLACE FUNCTION pa_pw_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar boolean;
   ispunct boolean;
   digitarray varchar2(20);
   chararray varchar2(52);

BEGIN
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

   -- Check if the password is same as the username
   IF upper(password) = upper(username) THEN
     raise_application_error(-20001, 'Password same as user');
   END IF;

   -- Check for the minimum length of the password
   IF length(password) < 8 THEN
      raise_application_error(-20002, 'Password length less than 8');
   END IF;

   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
      raise_application_error(-20002, 'Password too simple');
   END IF;

   -- Check if the password contains at least one letter, one digit and one
   -- punctuation mark.
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one digit and one character');
   END IF;
   -- 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one digit and one character');
   END IF;

   <<endsearch>>
   -- Check if the password differs from the previous password by at least
   -- 5 letters
   IF old_password = '' THEN
      raise_application_error(-20004, 'Old password is null');
   END IF;
   -- Everything is fine; return TRUE ;
   differ := length(old_password) - length(password);

   IF abs(differ) < 5 THEN
      IF length(password) < length(old_password) THEN
         m := length(password);
      ELSE
         m := length(old_password);
      END IF;
      differ := abs(differ);
      FOR i IN 1..m LOOP
          IF substr(password,i,1) != substr(old_password,i,1) THEN
             differ := differ + 1;
          END IF;
      END LOOP;
      IF differ < 5 THEN
          raise_application_error(-20004, 'Password should differ by at least 5 characters');
      END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/
------------------

select (case
          when rn = 1 then
           ('create profile ' || profile ||' limit ' || resource_name ||' ' || limit || (case
          when rn = max_rn then
           ';'
        end)) else(resource_name || ' ' || limit || (case
         when rn = max_rn then
          ';'
       end)) end)
from (select profile,
               resource_name,
               limit,
               row_number() over(partition by profile order by profile, resource_name) rn,
               count(*) over(partition by profile) max_rn
          from dba_profiles@DBLINK_MIGUP
         where profile in
               (select distinct profile
                  from dba_profiles@DBLINK_MIGUP
                minus
                select distinct profile from dba_profiles)
         order by profile, resource_name)
order by profile, resource_name;
---------------------------------------
users.sql

select 'CREATE USER ' || u.username || ' IDENTIFIED BY VALUES ''' ||
       s.password || ''' DEFAULT TABLESPACE ' || 'USERS_19c' ||
       ' temporary TABLESPACE temp ' || ' PROFILE ' ||
       u.profile ||
       decode(u.account_status, 'EXPIRED', ' PASSWORD EXPIRE', '') ||
       ' ACCOUNT ' || decode(u.account_status, 'LOCKED', 'LOCK', 'UNLOCK') || ';'
from dba_users@DBLINK_MIGUP u, sys.user$@DBLINK_MIGUP s
where u.username = s.name and u.username not in (select username from dba_users);
-------------------------------------
dblink.sql
select distinct 'grant create database link to ' || owner || ';'
from dba_db_linkS@DBLINK_MIGUP
where owner not in ( select grantee from dba_sys_privs dp where dp.privilege like upper('create database link'));

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: create_users      8
检查: check_users        9
选择: grant_user_role_sys_privilege     10
检查: check_user_role_sys_privilege      11

/paic/app/oracle/rdbms/poracle/user_role_sys_privilege.sql

DBA

李成伟

创建失败的用户需要重置密码之后，再创建。不然表空间元数据和数据库元数据导入的时候会报错
select dbms_metadata.get_ddl('USER','GACMGR') from dual;
account unlock状态的有2个用户GACMGR和CKCC
查看create_user.log将所有报错的用户手工创建一遍

~~停WGQ_DG APPLY后重刷depcow库并起库~~

OLD_WGQ_DG

【预估时间,保证22点可以起库约提前60分钟开始】

CHANGE5处理

CHANGE5

~~CHANGE5~~

屏蔽旧库监控和报警

opcm/zabbix

DBA

李成伟

【旧库】停止和屏蔽生产库后台cron，

OLD_PRD
OLD_GM_DG
OLD_WGQ_DG

crontab -l > cron_backup813
crontab cron_null
crontab -l

aq_tm_processes integer 1
job_queue_processes integer 1600

cd /paic/tmp/nasshare3/licw/gbs
./t.sh
选择: backup_crontab
检查: check_ backup_crontab     --手动执行 crontab 备份
crontab -l > cron_backup813
crontab cron_null
crontab -l
alter system set job_queue_processes=0 ;
alter system set aq_tm_processes=0 scope=spfile;
exec dbms_scheduler.disable('XXXXXX' );

select round((sysdate - a.this_date) * 24 * 60, 2) elapsed_min,
       a.sid, b.serial#,a.job, a.this_date, b.username
From dba_jobs_running a, v$session b where a.sid = b.sid;
select * from dba_scheduler_running_jobs;

DBA

李成伟

禁止job运行：修改job和aq参数为0，

OLD_PRD

alter system set job_queue_processes=0 scope=both;
alter system set aq_tm_processes=0 scope=both;
show parameter job_queue_processes;
show parameter aq_tm_processes;

cd /paic/tmp/nasshare3/licw/gbs
./s.sh
选择: disable_job_aq_parameter ----手动执行 --原数据100 / 1
检查: check_job_aq_parameter

DBA

李成伟

通知冻结VCS

OLD_PRD

CHANGE5

1. 备份dba_jobs
2. 禁用JOB

OLD_PRD

备份job
DROP table migups.jobs_backup_20220715;
DROP table   migups.scheduler_backup_20220715;
create table migups.jobs_backup_20220715 as select * from dba_jobs ;
create table migups.scheduler_backup_20220715 as select * from dba_scheduler_jobs j;
select count(1) from migups.jobs_backup_20220715;
select count(1) from migups.scheduler_backup_20220715;

设置job最大
begin
   for cur in ( select * from dba_jobs j where j.BROKEN = 'N' ) loop
sys.dbms_ijob.next_date(job => cur.job,next_date=>to_date('3999/9/9','yyyy/mm/dd')) ;
   end loop ;
       commit ;
end;
/
begin
    for cur in (select 'begin sys.dbms_scheduler.disable('||''''||owner||'.'||job_name||''''||'); end;' hah from migups.scheduler_backup_20220715 where owner not in ('SYS'))
    loop
     execute immediate cur.hah;
     end loop;
     commit;
end;
/

检查
select job from dba_jobs j where j.next_date < to_date('3999-09-09','yyyy-mm-dd') and j.BROKEN = 'N' ;

cd /paic/tmp/nasshare3/licw/gbs
./s.sh
选择 check_running_jobs          14
如果需要: kill_running_jobs       19
选择: backup_jobs                     17
检查: check_backup_jobs

选择: set_jobs_next_date              18
检查: check_backup_jobs
select job from dba_jobs j where j.next_date < to_date('3999-09-09','yyyy-mm-dd') and j.BROKEN = 'N';

DBA

李成伟

里程碑, 领导(书安)决策, 变更开始

通知各平台修改连接串

关联平台

通知kettle老平台（王勇）、linkdo平台（刘斌，涉及KETTLE老平台连接串修改）、mario平台（宋明强）, 部署平台（叶方敏）修改连接串

不修改域名和vip，不涉及

DBA

李成伟

停止以gbs为目标的GG D和R进程

GG链路

停止以gbs为目标的D和R进程

DBA

王莎

更改旧生产上业务监听端口, 以及停止旧生产上所有的业务监听进程

OLD_PRD

lsnrctl stop gbs
lsnrctl stop gbs1527
cd $ORACLE_HOME/network/admin/
cp listener.ora listener.ora.20220715.BAK
编辑listener.ora ，只保留xtts的静态监听，其余都删除

lsnrctl stop gbs
lsnrctl stop gbs1527
lsnrctl stop gbs1528
cd $ORACLE_HOME/network/admin/
cp listener.ora listener.ora.20210417.BAK
编辑listener.ora ，只保留xtts的静态监听，其余都删除

DBA

李成伟

检查数据库大事务
未终止会话发给开发、运营确认是等待还是kill session

OLD_PRD

chk_db
select name,value from v$parameter p where p.NAME in ('open_links');
select 'BCT_STATUS',STATUS from v$block_change_tracking;

--check_session
select username,MACHINE,COUNT(1) CNT from v$session GROUP BY USERNAME,MACHINE;

select 'undo more then 100M sessions' from dual;
SELECT * FROM (SELECT s.STATUS sess_status, T.STATUS,S.INST_ID,S.SID,S.SQL_ID,s.SERIAL#, S.USERNAME,S.MACHINE,T.START_TIME, S.LOGON_TIME,T.USED_UREC RELATED_ROWS, ROUND(T.USED_UBLK*BLOCK_SIZE/1024/1024,2) UNDO_SIZE_M
     FROM GV$SESSION S, GV$TRANSACTION T, DBA_TABLESPACES DT,GV$SYSTEM_PARAMETER P
     WHERE UPPER(P.NAME)='UNDO_TABLESPACE' AND DT.TABLESPACE_NAME=P.VALUE AND
     T.INST_ID=S.INST_ID AND
     P.INST_ID=S.INST_ID AND
     S.SADDR = T.SES_ADDR and ROUND(T.USED_UBLK*BLOCK_SIZE/1024/1024,2)>100
     ORDER BY T.USED_UBLK DESC) WHERE ROWNUM<20;

select 'long ops session ' from dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
COLUMN OPNAME FORMAT A30;
col sid for a20
col event for a20
COLUMN MESSAGE FORMAT A60;
COLUMN START_T FORMAT A20;
COLUMN USERNAME FORMAT A10;
COLUMN MINUTES_REMAINING FORMAT 999999.9;
column kill for a20;
col COMPLETE_RATE for 99.9;
col LOGON_TIME for a30;
SELECT ''''||S.SID||','||S.SERIAL#||',@'||s.INST_ID||'''' kill, S.EVENT,MESSAGE,L.SQL_ID,L.USERNAME,/*L.OPNAME,*/TO_CHAR(L.START_TIME,'YYYY-MM-DD HH24:MI:SS') START_T,/*TO_CHAR(L.LAST_UPDATE_TIME,'YYYY-MM-DD HH24:MI:SS') UPDATE_T,*/ROUND
(L.TIME_REMAINING/60,1) MINUTES_REMAINING,ROUND(SOFAR/TOTALWORK*100,1) COMPLETE_RATE,S.LOGON_TIME FROM GV$SESSION_LONGOPS L, GV$SESSION S
WHERE s.INST_ID=l.INST_ID and S.SID=L.SID AND L.SERIAL#=S.SERIAL# AND L.TIME_REMAINING>0 ORDER BY TIME_REMAINING ASC NULLS FIRST;

select 'top event ' from dual;
with topevent as (select inst_id, RANK() OVER(ORDER BY COUNT(1) DESC) AS RANK,ROUND(RATIO_TO_REPORT(COUNT(1)) OVER()*100,1) RATIO,
               COUNT(1) CNT,
               EVENT
          FROM gV$SESSION s WHERE s.WAIT_CLASS <> 'Idle' group by inst_id,event)
select * from topevent where rank<20;

select 'top temp usage session ' from dual;
col username for a20
col program for a20
col tablespace for a20
col segtype for a10
col blocks for 999,999,999,999,999
select * from (SELECT se.username, ''''||Se.SID||','||Se.SERIAL#||',@'||se.INST_ID||'''' kill , se.machine, se.program, su.TABLESPACE,su.segtype, su.BLOCKS from
gv$session se, gv$sort_usage su WHERE se.saddr = su.session_addr and se.INST_ID=su.INST_ID
order by blocks desc) where rownum<5;

kill_session_not_from_local
ps -ef |grep -v grep |grep LOCAL=NO |grep $ORACLE_SID |awk '{print $2}' |wc -l
ps -ef |grep -v grep |grep LOCAL=NO |grep $ORACLE_SID |awk '{print $2}' |xargs kill -9

cd /paic/tmp/nasshare3/licw/gbs
./s.sh
选择 : chk_db            1
选择: kill_session_not_from_local    21
ps -ef |grep -v grep |grep LOCAL=NO |grep $ORACLE_SID |awk '{print $2}' |wc -l
ps -ef |grep -v grep |grep LOCAL=NO |grep $ORACLE_SID |awk '{print $2}' |xargs kill -9
检查: check_session          20

DBA

李成伟

重启数据库
关闭生产数据库前，手动切换几组redo日志。并设置STREAM SIZE到5G

OLD_PRD

alter system set streams_pool_size=5G scope=spfile;
alter system switch logfile ;
alter system switch logfile ;
alter system switch logfile ;
alter system checkpoint ;
shutdown immediate;
startup ;

cd /paic/tmp/nasshare3/licw/gbs
./s.sh

选择: restart_db_set_stream_size 31

stream参数影响expdp，需要确认是否修改成功

DBA

李成伟

检查导出参数STREEM POOL

OLD_PRD

show parameter streams_pool_size

cd /paic/tmp/nasshare3/licw/gbs
./s.sh
检查:check_streams_pool_size 32

DBA

李成伟

停止以gbs为源的GG链路的E、D, 目标库R进程

OLD_PRD

重启抽取进程，无延迟后停止以gbs为源的GG链路的E、D, 目标库R进程

DBA

王莎

将表空间设置为read only
切换几次redo日志
检查并确保read only log传递到DG库

OLD_PRD

select 'alter tablespace '||TBS.TABLESPACE_NAME || ' read only;'from dba_tablespaces tbs where tbs.tablespace_name in (select * from migups.s_tbs_list) AND STATUS='ONLINE';
select TBS.TABLESPACE_NAME ,STATUS from dba_tablespaces tbs where tbs.tablespace_name in (select * from migups.s_tbs_list) /* OR tablespace_name in ('SYSTEM','SYSAUX') */ AND STATUS='ONLINE' order by STATUS desc;
alter system switch logfile;

先需要确认GG业务链路都已经停止
cd /paic/tmp/nasshare3/licw/gbs
sh s.sh
选择 set_tbs_readonly         33
检查: chk_tbs_readonly        34

检查并确保read only log传递到DG库                  alter system switch logfile;
确认DG是READ ONLY
select status,count(*) from dba_tablespaces group by status;

DBA

李成伟

【旧观澜DG】最后一次XTTS增备
重点关注备份效率、每10分钟通报一次进展和预估完成时间

OLD_GL_DG

cd /paic/tmp/nasshare3/licw/gbs
./s.sh   ./sgm.sh ./swgq.sh
选择 xtts_incr_backup      5
检查进度: check_xtts_incr_backup       6
确认增备完成后
选择 xtts_incr_update_from_scn 以更新下次增备的起点         7

DBA

李成伟

【新生产】最后一次XTTS恢复
重点关注恢复效率、每10分钟通报一次进展和预估完成时间

NEW_PRD

/bin/rm -rf /paic/tmp/nasshare/licw/gbs/target/tmpdir/FAILED
X86目标库做应用增备恢复

mv /paic/tmp/nasshare/licw/gbs/source/xtts/incr /paic/tmp/nasshare/licw/gbs/source/xtts/incr.`date +%Y%m%d`.`date +%H%M`
mkdir -p /paic/tmp/nasshare/licw/gbs/source/xtts/incr
mkdir -p /paic/tmp/nasshare/licw/gbs/source/xtts/tmpdir
chmod 777 /paic/tmp/nasshare/licw/gbs/source/xtts/incr

cp /paic/tmp/nasshare3/licw/gbssource/xtts/tmpdir/tsbkupmap.txt /paic/tmp/nasshare3/licw/gbstarget/tmpdir/tsbkupmap.txt

cd /paic/tmp/nasshare/licw/gbs/target/xtts
vi apply_incr.sh
TMPDIR=/paic/tmp/nasshare/licw/gbs/target/tmpdir
XTTDEBUG=1
export TMPDIR XTTDEBUG
cd ./xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r
nohup sh apply_incr.sh >apply_incr.log 2>&1 &

DBA

李成伟

【旧库】导出表空间元数据（不需要等待同城、远程增量恢复）【并行】
导出前复核_hash_join_enabled=false,否则影响导出效率

OLD_PRD

userid='/ as sysdba'
directory=TTS_EXP
dumpfile=expdp_tbsmeta.dmp
logfile=expdp_tbsmeta.log
transport_full_check=y
exclude=TABLE_STATISTICS,INDEX_STATISTICS
transport_tablespaces=GBSJOB,GRESVDATA,GBSMANDATA,GBSMAN,EGISPRINTDATA,QUEST,TOOLS,GBSMIS,GBSREPT,RESPMAN,TS_CMCC,GBSMAN_INDEX,GIMSDATA,EGISNBUHISDATA,GBSARC,EGISIPSDATA,USERS,GBSLOAD,GUSERMGR,GBSREP,GBSLOGTMPIDX,HSCQDDATA,EGISRMSDATA,APIDATA,PAUSERS,GBSUSERS,GBSCKCC,HBCTDATA,GBSTRG,ABBSDATA,EGISCLAIMPRINTDATA,G2LGBSCDE,EGISNBUDATA,DBADATA,GBSINDEX,ACCTMAN,GBSBASE,TS_CTCC,GBSAQ,CIF_TRANS,MIDSTORG_GBS_ARC,HSPDATA,GRESVIDX,GBSLOGTMPDATA,EGISCHNDATA,EGISQRYDATA,DMLBAKDATA,AUDDATA,GBS,CHNL_DUMP,GREMAN,VOUDATA
METRICS=YES
JOB_NAME=expdp_tbsmeta
nohup expdp parfile=expdp_tbsmeta.para > expdp_tbsmeta.log 2>&1 &
chmod 755 /paic/gbs/gbs/data/opgbs/xtts_exp/expdp_tbsmeta.dmp
dscp padba@g4as6020:/paic/gbs/gbs/data/opgbs/xtts_exp/expdp_tbsmeta.dmp padba@cnsz083547:/paic/tmp/nasshare/licw/gbs/dmp --传输文件

cd /paic/tmp/nasshare3/licw/gbs
./s.sh
选择: expdp_tbsmeta                  35
检查: check_expdp_tbsmeta        38

/paic/tmp/nasshare3/licw/gbsdmp/expdp_tbsmeta.para      --source
/paic/tmp/nasshare/licw/gbs/dmp/impdp_tbsmeta.para      --target       --提前修改参数文件

DBA

~~李成伟~~

【旧库】导出全库元数据【并行】

OLD_PRD

vi expdp_fullmeta.para
userid='/ as sysdba'
directory=TTS_EXP
dumpfile=expdp_fullmeta.dmp
logfile=expdp_fullmeta.log
full=Y
exclude=TABLE_STATISTICS,INDEX_STATISTICS,materialized_view,materialized_view_log,NORMAL_OPTIONS,VIEWS_AS_TABLES
content=metadata_only
METRICS=YES
nohup expdp parfile=expdp_fullmeta.para > expdp_fullmeta.log 2>&1 &
chmod 755 /paic/gbs/gbs/data/opgbs/xtts_exp/expdp_fullmeta.dmp
dscp padba@g4as6020:/paic/gbs/gbs/data/opgbs/xtts_exp/expdp_fullmeta.dmp padba@cnsz083547:/paic/tmp/nasshare/licw/gbs/dmp --传输文件

cd /paic/tmp/nasshare3/licw/gbs
./s.sh
选择: expdp_fullmeta 36
检查: check_expdp_fullmeta 39

DBA

李成伟

检查一遍DG的情况

NEW_GM_DG
NEW_WGQ_DG

set lines 12000
set pages 999
col process for a10
col status for a10
col sequence# for 9999999999999999
col inst_id for 99
col THREAD# for 9
select process,status,sequence# from v$managed_standby;
select name,value,datum_time from v$dataguard_stats;
select INST_ID,PROCESS,STATUS,THREAD#,SEQUENCE# ,DELAY_MINS from gv$managed_standby where process like 'MRP%';

cd /paic/tmp/nasshare/licw/gbs/
./tgm.sh ./t.wgq.sh 选择: check_dg

确保如下参数有配置：
光明DG：
Alter system set db_file_name_convert='+DATA_GBS_DG/GBS/DATAFILE/','+DATA_GBS_DG/LGBS/DATAFILE/'scope=spfile;

外高桥DG：
Alter system set db_file_name_convert='+DATA_GBS_DG/GBS/DATAFILE/','+DATA_GBS_DG/RGBS/DATAFILE/' scope=spfile;

DBA

李成伟

(2点)里程碑旧库所有数据完成导出.(书安,老王)(关键点: 项目组评估整体进度是否正常以及是否需要回退)

提前关闭掉DB LINK参数以及AUDIT

NEW_PRD

ps -ef |grep poracle |grep YES|grep -v grep |awk '{print $2}'|xargs kill -9
alter system set open_links=1 scope=spfile;
alter system set audit_trail=NONE scope=spfile;
重启DB
shutdown immediate;
startup

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: restart_db_disable_audit_dblink 12 --手动处理
检查: check_audit_dblink 13

DBA

李成伟

生成导入表空间的脚本

NEW_PRD

vi /paic/tmp/nasshare/licw/gbs/target/xtts/tbs_meta.sh
TMPDIR=/paic/tmp/nasshare/licw/gbs/target/tmpdir
XTTDEBUG=1
export TMPDIR XTTDEBUG
cd ./xtts
perl xttdriver.pl -e

vi /paic/tmp/nasshare/licw/gbs/dmp/impdp_tbsmeta.para
userid='/ as sysdba'
directory=tts_imp
dumpfile=expdp_tbsmeta.dmp
logfile=impdp_tbsmeta.log
tail -1 /paic/tmp/nasshare/licw/gbs/target/tmpdir/xttplugin.txt>>/paic/tmp/nasshare/licw/gbs/dmp/impdp_tbsmeta.para

拷贝 tsbkupmap.txt ，xttplan.txt 文件
dscp padba@g4as5040:/paic/tmp/nasshare3/licw/gbs/source/xtts/tmpdir/xttplan.txt padba@cnsz083547:/tmp
dscp padba@g4as5040:/paic/tmp/nasshare3/licw/gbs/source/xtts/tmpdir/tsbkupmap.txt padba@cnsz083547:/tmp
cp /tmp/xttplan.txt /paic/tmp/nasshare/licw/gbs/target/tmpdir
cp /tmp/tsbkupmap.txt /paic/tmp/nasshare/licw/gbs/target/tmpdir

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: prepare_impdp_tbsmeta_para 15
检查: check_prepare_impdp_tbsmeta_para 16

cd /paic/tmp/nasshare/licw/gbs/target/xtts
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
nohup sh tbs_meta.sh>tbs_meta.log 2>&1 &
ls -trl /paic/tmp/nasshare/licw/gbs/target/tmpdir/xttplugin.txt
会生成一个文件xttpplugin.txt，这个文件是表空间导入的DBLINK模式。
主要取这个文件的datafile部分，生成导入PAR文件。

DBA

李成伟

impdp表空间元数据时使用transportable=no_bitmap_rebuild

【新库】表空间元数据导入 (排除掉TRIGGER)

NEW_PRD

DROP directory tts_imp;
create directory tts_imp as '/paic/tmp/nasshare/licw/gbs/dmp';
grant read,write on directory tts_imp to public;

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
cd /paic/tmp/nasshare/licw/gbs/dmp
nohup impdp parfile=impdp_tbsmeta.para > impdp_tbsmeta.log 2>&1 &

select owner_name ,job_name,operation,job_mode from DBA_DATAPUMP_JOBS where state='EXECUTING';

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: impdp_tbsmeta 17
检查: check_impdp_tbsmeta 18

DBA

李成伟

关键点: 表空间完成导入, 项目组评估整体进度是否正常以及是否需要回退(书安,老王)

【新库】设置传输表空间为read write
【新库】复核新库输表空间为read write

NEW_PRD

select * from migups.s_tbs_list@DBLINK_MIGUP

set heading off
select 'alter tablespace '||TBS.TABLESPACE_NAME || ' read write;'from dba_tablespaces tbs where tbs.tablespace_name in (select * from migups.s_tbs_list@DBLINK_MIGUP) and STATUS!='ONLINE';
select TBS.TABLESPACE_NAME ,STATUS from dba_tablespaces tbs where tbs.tablespace_name in (select * from migups.s_tbs_list@DBLINK_MIGUP) /* OR tablespace_name in ('SYSTEM','SYSAUX') */ order by STATUS desc;

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择:set_tbs_readwrite 22
选择:chk_tbs_readonly 23

DBA

李成伟

【新库】创建回滚点BEFORE_FULLDB_IMPDP，在创建回滚点前后切换3次redo

NEW_PRD

alter system switch logfile ;
alter system checkpoint ;
create restore point BEFORE_FULLDB_IMPDP guarantee flashback database;
alter system switch logfile ;
alter system switch logfile ;
alter system switch logfile ;
alter system checkpoint ;

select NAME, t.GUARANTEE_FLASHBACK_DATABASE,t.RESTORE_POINT_TIME,
t.STORAGE_SIZE from v$restore_point t;

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: create_BEFORE_FULLDB_IMPDP_restore_point 20
检查: check_BEFORE_FULLDB_IMPDP_restore_point 21

DBA

李成伟

【新库】X86生产环境导入全库metadata

NEW_PRD

DROP DIRECTORY impdp_full;
CREATE OR REPLACE DIRECTORY impdp_full AS '/paic/tmp/nasshare/licw/gbs/dmp';
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

vi impdp_fullmeta.para
userid='/ as sysdba'
directory=impdp_full
dumpfile=expdp_fullmeta.dmp
logfile=impdp_full.log
full=y
content=metadata_only
table_exists_action=skip

nohup impdp parfile=impdp_fullmeta.para > impdp_full.log 2>&1 &

select owner_name ,job_name,operation,job_mode from DBA_DATAPUMP_JOBS where state='EXECUTING';

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: impdp_fullmeta 24
检查: check_impdp_fullmeta
发现进程已经起来了以后, 创建索引
选择: create_impdp_fullmeta_index 25

create index sys.IX_IMPDP_FULLMETA_PPO_KEN on IMPDP_FULLMETA(parent_process_order) online;
begin
dbms_stats.gather_table_stats(ownname => 'SYS' ,tabname =>'IMPDP_FULLMETA' ,cascade => true,method_opt => 'FOR ALL columns size SKEWONLY');
end;
/
select owner,index_name,uniqueness,status from dba_indexes where index_name in ('IX_IMPDP_FULLMETA_PPO_KEN');

DBA

李成伟

迁移migups 用户数据

OLD_PRD/NEW_PRD

DROP DIRECTORY expdp_full;
CREATE OR REPLACE DIRECTORY expdp_full AS '/paic/tmp/nasshare3/licw/gbsdmp';
grant read,write on DIRECTORY expdp_full to public;

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
导出:
vi /paic/gbs/gbs/data/opgbs/xtts_exp/expdp_migups.par
userid='/ as sysdba'
directory=TTS_EXP
dumpfile=expdp_migups.dmp
logfile=expdp_migups.log
SCHEMAS=migups
exclude=TABLE_STATISTICS,INDEX_STATISTICS
CONTENT=DATA_ONLY
METRICS=YES

nohup expdp parfile=expdp_migups.par > expdp_migups.log 2>&1 &

chmod 755 /paic/gbs/gbs/data/opgbs/xtts_exp/expdp_migups.dmp
dscp padba@g4as6020: /paic/gbs/gbs/data/opgbs/xtts_exp/expdp_migups.dmp padba@cnsz083547:/paic/tmp/nasshare/licw/gbs/dmp --传输文件

导入：
vi impdp_migups.par
userid='/ as sysdba'
directory=impdp_full
dumpfile=expdp_migups.dmp
logfile=impdp_migups.log
CONTENT=DATA_ONLY
SCHEMAS=migups

nohup impdp parfile=impdp_migups.par > impdp_migups.log 2>&1 &

改成迁移全库导入后遗漏的quest用户对象

关键点: 全库元数据完成导入, 项目组评估整体进度是否正常以及是否需要回退(书安,老王)

【新库】TRIGGER导入

NEW_PRD

vi impdp_trigger.par
userid='/ as sysdba'
directory=impdp_full
dumpfile=expdp_fullmeta.dmp
logfile=impdp_full_trigger.log
full=y
content=metadata_only
INCLUDE=trigger
table_exists_action=skip

nohup impdp parfile=impdp_trigger.par > impdp_full_trigger.log 2>&1 &

select owner,count(*) from dba_objects where object_type='TRIGGER' GROUP BY owner ORDER BY 2;

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: impdp_trigger 27
检查: check_impdp_trigger 31

DBA

李成伟

编译失效对象

NEW_PRD

$ORACLE_HOME/rdbms/admin/utlrp.sql；
select value from v$parameter where name='parallel_max_servers';
alter system set parallel_max_servers=16 scope=memory ;
@$ORACLE_HOME/rdbms/admin/utlrp.sql
alter system set parallel_max_servers=800 scope=memory;

SELECT COUNT(*) INVALID_OBJECTS_REMAINING FROM obj$ WHERE status IN (4, 5, 6);
SELECT COUNT(*) INVALID_OBJECTS_COMPLIED FROM UTL_RECOMP_COMPILED;
SELECT job_name JOB_CREATED FROM dba_scheduler_jobs where job_name like 'UTL_RECOMP_SLAVE_%';
SELECT job_name JOB_RUNNING FROM dba_scheduler_running_jobs WHERE job_name like 'UTL_RECOMP_SLAVE_%';

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: complie_objects_utlprp 28
检查: check_complie_objects_utlprp 29

---注意dblink引用

DBA

李成伟

复核：全库编译是否完成

NEW_PRD

select count(*) from dba_objects where status='INVALID';

select object_type,count(*) from dba_objects where status='INVALID'
group by object_type order by 2 desc;

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: check_complie_objects_utlprp 29

DBA

李成伟

重启并开启DBLINK参数以及AUDIT

NEW_PRD

set lines 12000
set pages 999
alter system set open_links=50 scope=spfile;
alter system set audit_trail=DB scope=spfile;
alter system set aq_tm_processes=0 scope=both;
alter system set job_queue_processes=0 scope=both;
shutdown immediate;
startup;

cd /paic/tmp/nasshare/licw/gbs/
./t.sh

选择:restart_db_enable_audit_dblink 30
检查:check_audit_dblink 13

DBA

李成伟

【新库】 SPM和统计信息处理放后台处理
1. SPM导入【并行】
2. 统计信息导入

NEW_PRD

执行/paic/tmp/nasshare/licw/gbs/target/script/unpack_spm.sql
检查:select count(1) SPM_COUNT from dba_sql_plan_baselines;

再执行/paic/tmp/nasshare/licw/gbs/target/script/import_stat.sql

检查：
select count(1) SPM_COUNT from dba_sql_plan_baselines;
select count(1) STAT_DIFF_COUNT
from dba_tab_statistics T WHERE NOT exists
(select 1 from MIGUPS.S_DBA_TAB_STATISTICS s
         where t.owner = s.owner
           and t.table_name = s.table_name
           and (t.PARTITION_NAME=s.PARTITION_NAME or (t.PARTITION_NAME is null and s.PARTITION_NAME is null)) and (t.SUBPARTITION_NAME=s.SUBPARTITION_NAME or (t.SUBPARTITION_NAME is null and s.SUBPARTITION_NAME is null))
           and ((t.last_analyzed is null and s.last_analyzed is null)
            or t.last_analyzed = s.last_analyzed)) and t.owner in (select username from migups.s_user_list) and last_analyzed is null ;

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择 unpack_spm      32
             import_stat    33
检查: check_spm
             check_stat

DBA

李成伟

【新库】收集数据字典统计信息和fixed object统计信息【非关键路径】放后台处理. 【并行】

NEW_PRD

/paic/tmp/nasshare/licw/gbs/target/script
begin
dbms_stats.GATHER_FIXED_OBJECTS_STATS;
dbms_stats.GATHER_DICTIONARY_STATS;
end;
/

/paic/tmp/nasshare3/licw/gbs
./t.sh
选择: gather_dict_fix_stat 36

DBA

李成伟

【新库】授予用户对象权限

NEW_PRD

set lines 12000
set pages 999
grant execute on LIFEMAN.LAS_REP_BANKVIREMENT_PKG to lifejob;
grant execute on DBMS_LOCK to GBSMAN;
grant execute on DBMS_LOCK to REINSDEV;
grant execute on SYS.DBMS_SHARED_POOL to dbmgr;
grant all on SYS.DBMS_AUDIT_MGMT to dbmgr;
grant execute on SYS.DBMS_CRYPTO to dbmgr;
grant execute on DBMS_REDEFINITION to dbmgr;
grant execute on DBMS_SERVICE to dbmgr;

spool user_role_privilege.sql

--USER DBS SYS
with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM MIGUPS.S_DBA_SYS_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE ||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee and rs.admin_option=rs.admin_option);

--USER TAB PRIV
with tg as (select PRIVILEGE,owner,table_name, GRANTEE FROM DBA_TAB_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select PRIVILEGE,owner,table_name, GRANTEE FROM MIGUPS.S_DBA_TAB_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';' from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.table_name=tg.table_name and rs.grantee=tg.grantee) and table_name not
like '%/%' order by GRANTEE;

--USER DBA ROLE
with tg as (select GRANTED_ROLE, GRANTEE,admin_option FROM DBA_ROLE_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select GRANTED_ROLE, GRANTEE,admin_option FROM MIGUPS.S_DBA_ROLE_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.GRANTED_ROLE=tg.GRANTED_ROLE and rs.grantee=tg.grantee ) ;

--ROLE DBA SYS
with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)) ),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM MIGUPS.S_DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)) )
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee) AND grantee not in ('DBA');

--ROLE DBA TAB
with tg as (select PRIVILEGE,owner,table_name, GRANTEE FROM DBA_TAB_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list))),
rs as (select PRIVILEGE,owner,table_name, GRANTEE FROM MIGUPS.S_DBA_TAB_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)))
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';' from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.table_name=tg.table_name and rs.grantee=tg.grantee) AND grantee not in ('DBA');

--TABLESPACE QUOTA

SELECT 'ALTER USER ' || USERNAME || ' QUOTA ' ||
       DECODE(MAX_BYTES, -1, 'UNLIMITED', MAX_BYTES) || ' ON ' ||
       TABLESPACE_NAME || ';'
FROM MIGUPS.S_DBA_TS_QUOTAS TS
WHERE USERNAME in
       (select username from migups.s_user_list)
   AND NOT EXISTS (SELECT 1
          FROM DBA_TS_QUOTAS TT
         WHERE TS.TABLESPACE_NAME = TT.TABLESPACE_NAME
           AND TS.USERNAME = TT.USERNAME
           AND TT.max_bytes = TS.MAX_BYTES) and TABLESPACE_NAME in (select name from v$tablespace)
union
select 'ALTER USER ' || USERNAME || ' QUOTA UNLIMITED ON ' ||
       DU.default_tablespace || ';'
from dba_users DU
where USERNAME in
       (select username from migups.s_user_list)
   AND NOT EXISTS (SELECT 1
          FROM DBA_TS_QUOTAS TS
         WHERE TS.TABLESPACE_NAME = DU.default_tablespace
           AND TS.USERNAME = DU.USERNAME);

--QUOTA UNLIMITED ON

SELECT 'ALTER USER '||du.USERNAME||' QUOTA UNLIMITED ON '||DU.default_tablespace||';' FROM MIGUPS.S_DBA_USERS DU,dba_users dut WHERE du.username=dut.username and du.default_tablespace!=dut.default_tablespace and DU.username IN (select username from migups.s_user_list) and DU.default_tablespace in (select name from v$tablespace);
--------------------------------------------

with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM MIGUPS.S_DBA_SYS_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select COUNT(1) NUM_DBA_SYS_NG2_USER from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee and rs.admin_option=rs.admin_option);

--USER TAB PRIV
with tg as (select PRIVILEGE,owner,table_name, GRANTEE FROM DBA_TAB_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select PRIVILEGE,owner,table_name, GRANTEE FROM MIGUPS.S_DBA_TAB_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select COUNT(1) NUM_DBA_TAB_NG2_USER from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.table_name=tg.table_name and rs.grantee=tg.grantee) and table_name not like '%/%' order by GRANTEE;

--USER DBA ROLE
with tg as (select GRANTED_ROLE, GRANTEE,admin_option FROM DBA_ROLE_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select GRANTED_ROLE, GRANTEE,admin_option FROM MIGUPS.S_DBA_ROLE_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select   COUNT(1) NUM_DBA_ROLE_NG2_ROLE from rs where not exists (select 1 from tg where rs.GRANTED_ROLE=tg.GRANTED_ROLE and rs.grantee=tg.grantee ) ;

--ROLE DBA SYS
with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)) ),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM MIGUPS.S_DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)) )
select   COUNT(1) NUM_DBA_SYS_NG2_ROLE from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee) AND grantee not in ('DBA');

--ROLE DBA TAB
with tg as (select PRIVILEGE,owner,table_name, GRANTEE FROM DBA_TAB_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list))),
rs as (select PRIVILEGE,owner,table_name, GRANTEE FROM MIGUPS.S_DBA_TAB_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)))
select   COUNT(1) NUM_DBA_TAB_NG2_ROLE from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.table_name=tg.table_name and rs.grantee=tg.grantee) AND grantee not in ('DBA');

--DEFAULT TABLESPACE

SELECT count(1) DEFAULT_TABLESPACE_DIFF FROM MIGUPS.S_DBA_USERS DU,dba_users dut WHERE du.username=dut.username and du.default_tablespace!=dut.default_tablespace and DU.username IN (select username from migups.s_user_list) and DU.default_tablespace in (select name from v$tablespace);

--TABLESPACE QUOTA
SELECT COUNT(1) TABLESPACE_QUOTA_DIFF FROM (
SELECT 'ALTER USER ' || USERNAME || ' QUOTA ' ||
       DECODE(MAX_BYTES, -1, 'UNLIMITED', MAX_BYTES) || ' ON ' ||
       TABLESPACE_NAME || ';'
FROM MIGUPS.S_DBA_TS_QUOTAS TS
WHERE USERNAME in
       (select username from migups.s_user_list)
   AND NOT EXISTS (SELECT 1
          FROM DBA_TS_QUOTAS TT
         WHERE TS.TABLESPACE_NAME = TT.TABLESPACE_NAME
           AND TS.USERNAME = TT.USERNAME
           AND TT.max_bytes = TS.MAX_BYTES) and TABLESPACE_NAME in (select name from v$tablespace)
union
select 'ALTER USER ' || USERNAME || ' QUOTA UNLIMITED ON ' ||
       DU.default_tablespace || ';'
from dba_users DU
where USERNAME in
       (select username from migups.s_user_list)
   AND NOT EXISTS (SELECT 1
          FROM DBA_TS_QUOTAS TS
         WHERE TS.TABLESPACE_NAME = DU.default_tablespace
           AND TS.USERNAME = DU.USERNAME));

/paic/tmp/nasshare3/licw/gbs
./t.sh
选择:grant_user_role_privilege 38
检查: compare_user_role_privilege 39

grant execute on DBMS_LOCK to GBSMAN;
grant execute on DBMS_LOCK to REINSDEV;

DBA

李成伟

执行不出调整动态采样，就能跑出

增加 RAC PROCESS_KILL 过程

42) deploy_rac_kill_procedure
43) check_deploy_rac_kill_procedure

【新库】19C新的统计信息程序包部署[并行]

NEW_PRD

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: deploy_new_Gath_stat 44
检查: check_deploy_new_Gath_stat 45

DBA

李成伟

更新SPM 收集程序

NEW_PRD

更新SPM (注意更新命令中的dbmgr密码)
cd /paic/tmp/nasshare/licw/gbs/target/script/initdb/SPM_V11.1
./install_spm_vll_lbs.sh yhsyxyz8
验证:
cat install.log

库重建WM_CONCAT

SALES_NEW_PRD

CREATE OR REPLACE TYPE APPMGR."WM_CONCAT_IMPL"                                          AS OBJECT
-- AUTHID CURRENT_USER AS OBJECT
(
CURR_STR clob,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
    RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
                                       P1   IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL,
                                         RETURNVALUE OUT clob,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
                                     SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY APPMGR."WM_CONCAT_IMPL" IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
    RETURN NUMBER IS
BEGIN
    SCTX := WM_CONCAT_IMPL(NULL);
    RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
                                       P1   IN VARCHAR2) RETURN NUMBER IS
BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL,
                                         RETURNVALUE OUT clob,
                                         FLAGS       IN NUMBER) RETURN NUMBER IS
BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
                                     SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER IS
BEGIN
    IF (SCTX2.CURR_STR IS NOT NULL) THEN
      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
    END IF;
    RETURN ODCICONST.SUCCESS;
END;
END;
/

CREATE OR REPLACE FUNCTION APPMGR.WM_CONCAT(P1 VARCHAR2) RETURN clob AGGREGATE USING WM_CONCAT_IMPL;
/
create public SYNONYM WM_CONCAT for APPMGR.WM_CONCAT;
grant EXECUTE on APPMGR.WM_CONCAT_IMPL to PUBLIC;
grant EXECUTE on APPMGR.WM_CONCAT to PUBLIC;

升级PA_AUDIT

NEW_PRD

vi /paic/tmp/nasshare/licw/gbs/target/script/PINGAN_AUDIT_UPGRADE.sql
alter user dbmgr identified by Paic1234;
conn dbmgr/Paic1234

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
46) upgrade_audit_package
47) check_upgrade_audit_package

清理旧监控用户

NEW_PRD

alter trigger gbsTRG.TR_DROPDENY_DEPEND_CHECK disable;
alter trigger gbsTRG.TR_DROPDENY_DEPEND_CHECK enable;

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
48) drop_abandom_users
49) check_drop_abandom_users

DBA

李成伟

复核：全库编译是否完成

NEW_PRD

select count(*) from dba_objects where status='INVALID';

select object_type,count(*) from dba_objects where status='INVALID' group by object_type;

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: check_complie_objects_utlprp 29

DBA

李成伟

复核：全库元数据导入完成后对比检查

NEW_PRD

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
检查:    check_rgbstry                              50
           check_migrate_invalid_objects      51
           compare_objects                          52
           check_invalid_objects                   53

DBA

李成伟

复核：迁移用户对象比较

NEW_PRD

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择:compare_objects 52 ----------与上步一致

DBA

李成伟

复核：检查统计是否正常导入

NEW_PRD

select to_char(stats_update_time,'yyyy-mm-dd hh24:mi') ,count(*)from dba_tab_stats_history
where owner in ( select username from migupd.s_user_list where flag='Y')
group by to_char(stats_update_time,'yyyy-mm-dd hh24:mi') order by 1;

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
检查: check_stat 35
select dbms_stats.get_param('PUBLISH') from dual; ----true

DBA

李成伟

复核：SPM是否导入

NEW_PRD

select a.cnt-b.cnt from (SELECT count(*) cnt FROM DBA_SQL_PLAN_BASELINES@DBLINK_MIGUP WHERE ACCEPTED='YES' and enabled='YES') a,(SELECT count(*) cnt FROM DBA_SQL_PLAN_BASELINES WHERE ACCEPTED='YES'and enabled='YES')b;

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
检查: check_spm

DBA

李成伟

复核：用户权限

NEW_PRD

select grantee,privilege from dba_sys_privs@dblink_migup
where grantee in (select username from migups.s_user_list)
minus
select grantee,privilege from dba_sys_privs
where grantee in (select username from migups.s_user_list);

---check role privs
select grantee,granted_role from dba_role_privs@dblink_migup
where grantee in (select username from migups.s_user_list)
minus
select grantee,granted_role from dba_role_privs
where grantee in (select username from migups.s_user_list);

---check tab privs
select owner,table_name,privilege,grantee from dba_tab_privs@dblink_migup
where grantee in (select username from migups.s_user_list) and owner not in ('SYS','IMSQUEUE')
minus
select owner,table_name,privilege,grantee from dba_tab_privs
where grantee in (select username from migups.s_user_list) and owner not in ('SYS','IMSQUEUE');

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择: grant_user_role_sys_privilege 10
检查: check_user_role_sys_privilege 11

DBA

李成伟

复核：光明DG、外高DG是否正常

NEW_GM_DG
NEW_WGQ_DG

cd /paic/tmp/nasshare/licw/gbs/
./tgm.sh ./twgq.sh
选择: check_DG

DBA

~~李成伟~~

复核：数据字典、FIXED OBJECT是否收集完成

NEW_PRD

select operation,to_char(START_TIME,'yyyy-mm-dd hh24:mi:ss'),to_char(END_TIME,'yyyy-mm-dd hh24:mi:ss'),status from dba_optstat_operations where operation in ('gather_dictionary_stats','gather_fixed_objects_stats')
and START_TIME > sysdate -1 order by 1 desc;

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择:check_fix_stat

DBA

李成伟

重建JOB, 设置JOB ENV

NEW_PRD

vi /paic/tmp/nasshare/licw/gbs/target/script/recreate_jobs.sql
alter user dbmgr identified by yhsyxyz8;
conn dbmgr/Paic1234

cd /paic/tmp/nasshare/licw/gbs/
./t.sh
选择:recreate_jobs 55
检查:check_recreate_jobs 56

新库注册catalog，同城打开BCT
并发起一次RMAN全备、监控备份进展(提前建好备份策略以及测试一次)

NEW_PRD
NEW_GM_DG

rman target / catalog RMAN19C_2020/rman19c_2020417@cat11g
rgbster database;
resync catalog ;
rman target / catalog RMAN19c_2020/rman19c_2020417@catdr
rgbster database;
resync catalog ;
同城:
alter database enable block change tracking using file '+data_lucd0_dg' reuse;
select * from v$block_change_tracking;

DBA

李成伟

观澜生产新库配置归档日志清理策略

NEW_PRD

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

DBA

李成伟

光明同城新库配置归档日志清理策略

NEW_GM_DG

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY BACKED UP 1 TIMES TO 'SBT_TAPE';

DBA

李成伟

外高桥容灾新库配置归档日志清理策略

NEW_WGQ_DG

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

DBA

李成伟

检查同城FLASHBACK开启

NEW_GM_DG

select FLASHBACK_ON from v$database;

【旧库】
恢复所有表空间为read write 【并行】(如果外高桥还没完成,要断DG关系)

OLD_PRD

select 'alter tablespace '||tablespace_name||' read write;' from dba_tablespaces where status='READ ONLY'
运行输出

sqlplus '/as sysdba'
show parameter job
show parameter aq

select 'alter tablespace '||tablespace_name||' read write;' from dba_tablespaces where status='READ ONLY'
select status,count(*) from dba_tablespaces group by status;

DBA

李成伟

【旧库】
disable trigger for GG 并复核
disable FK constraint for GG 并复核

OLD_PRD

cd /paic/tmp/nasshare3/licw/gbs/backup/fallbak/script
动态生成需要的脚本 @get_fk_tri.sql
然后运行:
@disable_constraint_r.sql
@disable_trigger.sql

DBA

王莎

【旧库】生产库只留XTTS监听

OLD_PRD

只留xtts临时监听
ps -fe |grep tns

DBA

李成伟

【新库】打开反向GG同步的E、D进程

NEW_PRD

DBA

王莎

【新库】迁移业务GG链路，恢复以X86新库为源的链路E,D进程

NEW_PRD

DBA

王莎

【旧库】打开反向GG同步的R进程，验证链路连通性

OLD_PRD

DBA

王莎

【新库】启动listener和service

NEW_PRD

启动后，注意复核端口、service的启动和分布情况

lsnrctl start gbs

DBA

李成伟

检查数据库INSTANCE数目(QD三个,GZ四个)

NEW_PRD
NEW_GM_DG
NEW_WGQ_DG

cd /paic/tmp/nasshare/licw/gbs
./t.sh
选择:chk_db

DBA

李成伟

DBA连通性验证

NEW_PRD

conn migupS/xxxx
select status from v$instance;

DBA

李成伟

授权

这是19c的预期行为，在19c后，对其他用户下的表进行update或insert 需要对这个表有select权限
请参考文档：Encountered ORA-01031: insufficient privileges When Executing An Update Statement On An Other Schema Objects (Doc ID 2641428.1)

select 'grant select on '||grantor||'.'||table_name||' to ' ||grantee||';' from dba_tab_privs where privilege in ('UPDATE','INSERT','DELETE');

里程碑 X86新生产库准备开放服务领导决策 (汪总,肖总)

域名/VIP切换，清空DNS缓存

NEW_PRD

域名解析到vip
gbs.db.paic.com.cn 10.33.30.207/10.33.30.237/10.33.30.239

DBA

李成伟/网络组

【新库】迁移业务GG链路

NEW_PRD

恢复以gbs为源的链路R进程和以gbs为目标的链路D和R进程

DBA

王莎

通知运营新生产已开放服务.启动业务验证.

运营确认应用验证正常后, 修改JOB参数

NEW_PRD

1、修改job 参数
alter system set aq_tm_processes=1 ;
alter system set job_queue_processes=50;
show parameter job
show paramete aq

DBA

李成伟

恢复新库的crontab

NEW_PRD

cd ~
mv .cron_file .cron_file.1005
mv cron_file_new .cron_file
/usr/bin/crontab $HOME/.cron_file

DBA

李成伟

安排数据补跑和job,

NEW_PRD

运营

恢复高频JOB

NEW_PRD

运营

DBA JOB 检查

NEW_PRD

DBA

删除RESTORE POINT

NEW_PRD

DROP restore point BEFORE_FULLDB_IMPDP ;

DBA

李成伟

恢复旧库的vcs

OLD_PRD/GM/WGQ

CHANGE5

启用监控（oem,zabbix)

NEW_PRD

DBA

李成伟

恢复小机环境dpcow,miscow定时刷新

NEW_PRD

DBA

李成伟

恢复UCMDB状态

NEW_PRD

DBA

李成伟

isw,monitor库处理

NEW_PRD

UPDATE database_info SET DB_IP_ADDRESS='10.33.30.207' where db_sid='GBS';
UPDATE database_info SET DB_VERSION='19.5.0' where db_sid='GBS';
UPDATE database_info SET DB_OS_USER='poracle' where db_sid='GBS';
UPDATE database_info SET DB_HOSTNAME='CNSZ083680,CNSZ083681,CNSZ083682,CNSZ083683' where db_sid='GBS';
UPDATE database_info SET DB_HOST_IP='10.33.30.207' where db_sid='GBS';

update database_info2 set dbversion='19.14.0' where instance_name='GBS';
update database_info2 set VIP='10.33.30.207' where instance_name='GBS';
update database_info2 set osuser='poracle' where instance_name='GBS';

commit;

DBA

李成伟

确认job补跑完成

NEW_PRD

运营

重置剩下DB JOB next_date

NEW_PRD

运营，部署

确认业务验证情况

NEW_PRD

运营

后续性能监控【升级后当天重点专人监控、并行期间告警优先处理】

NEW_PRD

DBA

李成伟

里程碑当天变更完成

MISCOW DEV COW

NEW MIS DEV COW

第二天刷库时完成域名切换

DBA

李成伟/网络组

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

oracle2

评论