1. 环境
|
项目 |
源库 |
目标库 |
|
IP |
10.132.63.250/251 |
10.132.103.84/85 |
|
VIP |
10.132.63.217/221 |
10.132.103.86/87 |
|
SCAN-IP |
10.132.63.252 |
10.132.103.88 |
|
操作系统 |
Red Hat Enterprise Linux Server release 7.9 |
麒麟V7 |
|
数据库版本 |
Oracle 11.2.0.4 |
Oracle 11.2.0.4 |
|
数据量/G |
121 |
|
2. 迁移时间
以下为迁移升级用时表。从测试时间记录来看总体需要1小时的数据库停机时间。
|
编号 |
主要操作 |
时间 |
阶段 |
是否停机 |
|
1 |
环境检查 |
1h |
环境准备 |
无需停机 |
|
2 |
数据库搭建DG参数配置 |
1h |
||
|
3 |
主备数据同步 |
2h |
||
|
4 |
配置申请DPM监控 |
1h |
收尾阶段 |
无需停机 |
3. 前期准备阶段
3.1 检查源库参数
3.1.1 检查数据库名
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string dbreinsure
db_unique_name string dbreinsure1
global_names boolean FALSE
instance_name string dbreinsure1
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string dbreinsure1
3.1.2检查源库归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 65606
Next log sequence to archive 65608
Current log sequence 65608
SQL> select NAME,FORCE_LOGGING,LOG_MODE from v$database;
NAME FOR LOG_MODE
--------- --- ------------
DBREINSURE YES ARCHIVELOG
3.2 搭建DATAGUARD配置
3.2.1 配置tnsora
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
源库配置
dbreinsure =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.135.0.86)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =10.135.0.87)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbreinsure)
)
)
reinsure101 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.135.42.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = reinsure)
)
)
reinsure103 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.135.42.103)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = reinsure)
)
)
dbreinsuredg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.135.66.72)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbreinsuredg)
)
)
zblsdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.135.37.81)(PORT = 2632))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zblsdg)
)
)
目标库配置
zblsdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.135.37.81)(PORT = 2632))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zblsdg)
)
)
dbreinsure =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.135.0.86)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =10.135.0.87)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbreinsure)
)
)
reinsure101 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.135.42.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = reinsure)
)
)
reinsure103 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.135.42.103)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = reinsure)
)
)
dbreinsuredg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.135.66.72)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbreinsuredg)
)
)
3.2.2 目标库配置静态监听
编辑监听文件
su - grid
cd $ORACLE_HOME/network/admin
vi listener.ora
添加静态监听
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=zblsdg)
(ORACLE_HOME=/picc/app/oracle/product/11.2/db1)
(SID_NAME=zblsdg1)
)
)
重新读取配置
lsnrctl reload
3.2.3 传输处理密码文件
sys密码：*********
MD5校验源库目标库密码文件一致性。
源端
[oracle@xpxtdb1 dbs]$ md5sum orapwdbreinsure1
[oracle@xpxtdb2 dbs]$ md5sum orapwdbreinsure2
目标端
[oracle@tyxppro1 dbs]$ md5sum orapwzblsdg1
[oracle@tyxppro2 dbs]$ md5sum orapwzblsdg2
3.2.4 配置白名单
修改目标库操作系统oracle用户密码
echo oracle:******|chpasswd--强密
授权源端访问目标端白名单
#systemctl restart sshd
3.2.5 修改源库参数
create pfile='/tmp/pfile.ora' from spfile;
alter system set log_archive_dest_3=’SERVICE=zblsdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=zblsdg'
3.2.6 目标库配置参数文件
zblsdg1.__db_cache_size=28118614016
zblsdg2.__db_cache_size=27715960832
zblsdg2.__java_pool_size=268435456
zblsdg1.__java_pool_size=268435456
zblsdg2.__large_pool_size=335544320
zblsdg1.__large_pool_size=335544320
zblsdg1.__oracle_base='/picc/app/oracle'#ORACLE_BASE set from environment
zblsdg2.__oracle_base='/picc/app/oracle'#ORACLE_BASE set from environment
zblsdg2.__pga_aggregate_target=10737418240
zblsdg1.__pga_aggregate_target=10737418240
zblsdg2.__sga_target=32212254720
zblsdg1.__sga_target=32212254720
zblsdg2.__shared_io_pool_size=0
zblsdg1.__shared_io_pool_size=0
zblsdg1.__shared_pool_size=3288334336
zblsdg2.__shared_pool_size=3690987520
zblsdg2.__streams_pool_size=0
zblsdg1.__streams_pool_size=0
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._row_cr=FALSE
*._serial_direct_read='NEVER'
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/picc/app/oracle/admin/zblsdg/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/zblsdg/controlfile/current.260.987935165'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATAC1/dbreinsure','+DATA/zblsdg'
*.db_files=5000
*.db_name='reinsure'
*.db_unique_name='zblsdg'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=1073741824000
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/picc/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zbdbXDB)'
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1','10262 trace name context forever, level 1048576'
*.fal_server='dbreinsure'
zblsdg1.instance_number=1
zblsdg2.instance_number=2
*.log_archive_config='DG_CONFIG=(reinsure,dbreinsure,dbreinsuredg,zblsdg)'
*.log_archive_dest_1='location=+DATA'
*.log_archive_dest_2='SERVICE=dbreinsure LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=dbreinsure'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+DATAC1/dbreinsure','+DATA/zblsdg'
*.open_cursors=1000
*.parallel_force_local=TRUE
*.pga_aggregate_target=10737418240
*.processes=1000
*.remote_listener='zbpro-scan:2632'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=1000
*.sga_max_size=32212254720
*.sga_target=32212254720
*.standby_file_management='AUTO'
zblsdg2.thread=2
zblsdg1.thread=1
*.undo_retention=9000
zblsdg2.undo_tablespace='UNDOTBS2'
zblsdg1.undo_tablespace='UNDOTBS1'
3.3 同步数据
3.3.1 连接测试
sqlplus sys/"********"@10.135.0.33:1521/dbreinsure as sysdba;
sqlplus sys/"********"@10.135.37.81:2632/zblsdg as sysdba;
rman target sys/"********"@dbreinsure auxiliary sys/"********"@zblsdg
3.3.2 目标库创建同步脚本
mkdir /home/oracle/work_dir/lzy -p
cd /home/oracle/work_dir/lzy
cat << "delimiter" > /home/oracle/work_dir/lzy/duplicate.sh
rman target sys/"********"@dbreinsure auxiliary sys/"********"@zblsdg << "EOF"
run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate channel p5 type disk;
allocate channel p6 type disk;
allocate auxiliary channel a1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
exit
EOF
delimiter
3.3.3 运行脚本同步数据
nohup sh /home/oracle/work_dir/lzy/duplicate.sh > /home/oracle/work_dir/lzy/duplicate.log &
3.4 目标端启动
3.4.1 启动目标端
alter database open;
3.4.2 处理standby log
select * from v$logfile where type='STANDBY' order by group#;
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;
alter database drop logfile group 19;
alter database drop logfile group 20;
alter database drop logfile group 21;
alter database drop logfile group 22;
#alter database clear unarchived logfile group 13;
#alter database clear unarchived logfile group 14;
alter database add standby logfile THREAD 1 group 13 '+DATA' size 4G;
alter database add standby logfile THREAD 1 group 14 '+DATA' size 4G;
alter database add standby logfile THREAD 1 group 15 '+DATA' size 4G;
alter database add standby logfile THREAD 1 group 16 '+DATA' size 4G;
alter database add standby logfile THREAD 1 group 17 '+DATA' size 4G;
alter database add standby logfile THREAD 1 group 18 '+DATA' size 4G;
alter database add standby logfile THREAD 1 group 19 '+DATA' size 4G;
alter database add standby logfile THREAD 1 group 20 '+DATA' size 4G;
alter database add standby logfile THREAD 1 group 21 '+DATA' size 4G;
alter database add standby logfile THREAD 1 group 22 '+DATA' size 4G;
alter database add standby logfile THREAD 1 group 23 '+DATA' size 4G;
alter database add standby logfile THREAD 2 group 24 '+DATA' size 4G;
alter database add standby logfile THREAD 2 group 25 '+DATA' size 4G;
alter database add standby logfile THREAD 2 group 26 '+DATA' size 4G;
alter database add standby logfile THREAD 2 group 27 '+DATA' size 4G;
alter database add standby logfile THREAD 2 group 28 '+DATA' size 4G;
alter database add standby logfile THREAD 2 group 29 '+DATA' size 4G;
alter database add standby logfile THREAD 2 group 30 '+DATA' size 4G;
alter database add standby logfile THREAD 2 group 31 '+DATA' size 4G;
alter database add standby logfile THREAD 2 group 32 '+DATA' size 4G;
alter database add standby logfile THREAD 2 group 33 '+DATA' size 4G;
alter database add standby logfile THREAD 2 group 34 '+DATA' size 4G;
3.4.3 启动实时同步应用
源库重启归档传输通道
#ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=defer;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;
目标库起MRP进程
alter database recover managed standby database using current logfile disconnect from session;
将数据库注册到集群中
srvctl add database -d zblsdg -n reinsure -o /picc/app/oracle/product/11.2/db1 -p +DATA/zblsdg/spfilezblsdg.ora -a data,crs
srvctl add instance -d zblsdg -i zblsdg1 -n zbpro1
srvctl add instance -d zblsdg -i zblsdg2 -n zbpro2
srvctl config database -d zblsdg
srvctl stop database -d zblsdg
alter system set cluster_interconnects='192.168.37.81' sid='zblsdg1' scope=spfile;
alter system set cluster_interconnects='192.168.37.83' sid='zblsdg2' scope=spfile;
srvctl start database -d zblsdg -o open
4. 收尾阶段
5.1 纳入监控
5.1.1 创建数据库监控用户并授权
create user dbamonitor identified by ********;
grant connect to dbamonitor;
grant select any dictionary to dbamonitor;
grant advisor to dbamonitor;
grant select_catalog_role to dbamonitor;
grant execute on sys.dbms_workload_repository to dbamonitor;
alter profile default limit password_life_time unlimited;
5.1.2 创建操作系统监控用户并授权
groupadd -g 1008 dba_mon
useradd -u 1008 -g dba_mon -G dba_mon,asmadmin dba_mon
echo "********" | passwd --stdin dba_mon
设置用户密码不过期
chage -M 99999 dba_mon
chage -l dba_mon
5.2 配置归档清理脚本
crontab -e
##delete archivelog
0 0 * * * sh /home/oracle/tools/clear_archivelog/del_archivelog.sh > /home/oracle/tools/clear_archivelog/del_archivelog.sh.log 2>&1
mkdir -p /home/oracle/tools/clear_archivelog
cd /home/oracle/tools/clear_archivelog
vi del_archivelog.sh
#OSTYPE
OSTYPE=`uname -s`
if [ $OSTYPE = "AIX" ]
then
. ~/.profile
else
. ~/.bash_profile
fi
rman target / << "delimiter"
crosscheck archivelog all;
delete force noprompt archivelog until time 'sysdate-3';
exit;
delimiter
