ADG迁移

斜风细雨时候

已于 2022-08-23 11:11:18 修改

阅读量550

点赞数

文章标签：数据库 oracle sql

于 2022-02-25 15:39:35 首次发布

本文链接：https://blog.csdn.net/qq_40699063/article/details/123134251

版权

本文详细介绍了如何配置Oracle数据库的主备库，包括主备库服务器的配置、归档模式设置、日志传输服务用户的创建、参数设置、监听配置、口令文件传输、数据库备份与恢复、数据同步测试等步骤，确保了主备库的正常运行和数据安全。

摘要由CSDN通过智能技术生成

主库：77.12.73.11
备库：77.12.73.27

1.主备库服务器配置服务名
主库：
su - oracle
cd /oracle/app/oracle/product/11.2.0/db_1/network/admin
vi tnsnames.ora
dw =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 77.12.73.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = dw)
)
)

备库
su - oracle
cd /oracle/app/oracle/product/11.2.0/db_1/network/admin
vi tnsnames.ora
dwadg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 77.12.73.27)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = dw)
)
)

2. 设置主库归档模式及force logging
检查归档路径状态：
select * from V$ARCHIVE_DEST_STATUS;
select * from V$ARCHIVE_DEST;
配置归档：
SQL> alter system set log_archive_dest_1='location=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' scope=both sid='*';
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
设置主库为force logging：
SQL> alter database force logging;
SQL> select force_logging from v$database;

3. 主库添加standby log
SQL> select group#, thread#, bytes/1024/1024 from v$log;

GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 2048
2 1 2048
3 1 2048
4 1 2048
5 1 2048
6 1 2048
7 1 2048
8 1 2048
9 1 2048
10 1 2048
select name from v$logfile;
配置standby log: (组数=（每个实例的日志组数+1）* 实例数） 11
alter database add standby logfile group 21 ('+data') size 2048m;
alter database add standby logfile group 22 ('+data') size 2048m;
alter database add standby logfile group 23 ('+data') size 2048m;
alter database add standby logfile group 24 ('+data') size 2048m;
alter database add standby logfile group 25 ('+data') size 2048m;
alter database add standby logfile group 26 ('+data') size 2048m;
alter database add standby logfile group 27 ('+data') size 2048m;
alter database add standby logfile group 28 ('+data') size 2048m;
alter database add standby logfile group 29 ('+data') size 2048m;
alter database add standby logfile group 30 ('+data') size 2048m;
alter database add standby logfile group 31 ('+data') size 2048m;

查看 standby log:
select group#, thread#, bytes/1024/1024 from v$standby_log;

4. 在主库上准备日志传输服务用户
SQL> create user dgdba identified by ora#1234 default tablespace system;
user created.
SQL> grant create session,sysoper to dgdba;
grant successed.

先通过spfile把pfile备份一下
create pfile='/oracle/software/tmppfile.ora' from spfile;
5. 设置主库参数
alter system set log_archive_config='DG_CONFIG=(dw,dwadg)' scope=both sid='*';
alter system set log_archive_dest_2='SERVICE=dwadg LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=dwadg' scope=both sid='*';
alter system set log_archive_dest_state_2=defer scope=both sid='*';
alter system set standby_file_management=auto scope=both sid='*';
alter system set fal_client='dw' scope=both sid='*';
alter system set fal_server='dwadg' scope=both sid='*';
alter system set log_file_name_convert='+data','+data','+arch','+arch' scope=spfile sid='*';
alter system set DB_FILE_NAME_CONVERT='+data','+data','+arch','+arch' scope=spfile sid='*';
alter system set redo_transport_user='DGDBA' scope=both sid='*';
alter system set log_archive_dest_state_2=defer scope=both sid='*';
(archive_dest_state_2可以在备库启动为mount状态之后，再修改为enable)

6. 配置主备库监听
cd /oracle/app/11.2.0/grid/network/admin
vi listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dw)
(SID_NAME = dw)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1) #oracle的家目录
(ENVS="TNS_ADMIN=/oracle/app/oracle/product/11.2.0/db_1/network/admin") #oracle的监听文档目录
)
)

备库添加：
su - grid
cd /oracle/app/11.2.0/grid/network/admin
vi listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dwadg_DGMGRL)
(SID_NAME = dw)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)
(ENVS="TNS_ADMIN=/oracle/app/oracle/product/11.2.0/db_1/network/admin")
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 77.12.73.27)(PORT = 1521))
)
)
重启监听：
su - grid

$ srvctl stop listener -l LISTENER
$ srvctl start listener -l LISTENER

备库操作

7. 备库创建相关路径（每个节点都要做）
mkdir -p /oracle/app/oracle/admin/dw/adump

8. 根据主库创建备库pfile
先在主库根据spfile创建pfile 并指定路径
create pfile='/oracle/software/tmp.ora' from spfile;
然后把创建的pfile传送到备库服务器
scp tmp.ora 77.12.73.27:/oracle/app/oracle/product/11.2.0/db_1/dbs/initdw.ora
然后到备库找到该文件，并进行编辑
vi initdw.ora

dw.__db_cache_size=52076478464
dw.__java_pool_size=939524096
dw.__large_pool_size=268435456
dw.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
dw.__pga_aggregate_target=19327352832
dw.__sga_target=57982058496
dw.__shared_io_pool_size=0
dw.__shared_pool_size=4294967296
dw.__streams_pool_size=0
*._adg_instance_recovery=TRUE
*._ash_size=33554432
*._cleanup_rollback_entries=2000
*._clusterwide_global_transactions=FALSE
*._external_scn_logging_threshold_seconds=600
*._external_scn_rejection_threshold_hours=24
*._gc_policy_time=0
*._library_cache_advice=FALSE
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._skip_trstamp_check=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/oracle/app/oracle/admin/dw/adump'
*.audit_trail='NONE'
*.commit_logging='IMMEDIATE'
*.commit_wait='NOWAIT'
*.compatible='11.2.0.4.0'
*.control_files='+DATA','+DATA','+DATA'
*.db_block_size=8192
*.db_cache_advice='OFF'
*.db_cache_size=64424509440
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=64
*.db_files=4096
*.db_name='dw'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/oracle/app/oracle'
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dwXDB)'
*.event='28401 trace name context forever, level 1','44951 trace name context forever, level 1024'
*.fal_server='dw'
*.filesystemio_options='SETALL'
*.java_pool_size=314572800
*.job_queue_processes=10
*.large_pool_size=209715200
*.log_archive_config='DG_CONFIG=(dw,dwadg)'
*.log_archive_dest_1='location=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_2='SERVICE=dw LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=dw'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=2
*.max_dump_file_size='500M'
*.memory_target=0
*.nls_date_format='YYYY-MM-DD HH24:MI:SS'
*.open_cursors=3000
*.open_links_per_instance=48
*.open_links=100
*.parallel_force_local=TRUE
*.parallel_max_servers=640
*.parallel_servers_target=256
*.pga_aggregate_target=10737418240
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=0
*.session_cached_cursors=200
*.sga_max_size=96636764160
*.sga_target=0
*.shared_pool_size=16106127360
*.streams_pool_size=524288000
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='dwadg'
*.log_file_name_convert='+data/dw/onlinelog','+data/dwadg/onlinelog'
*.db_file_name_convert='+data/dw/datafile','+data/dwadg/datafile'

9.传输主库口令文件到备库
cd /oracle/app/oracle/product/11.2.0/db_1/dbs
scp orapwdw 77.12.73.27:/oracle/app/oracle/product/11.2.0/db_1/dbs/orapwdw

10. 复制主库
创建备份脚本：
vi /back/dw_bak.sh
export ORACLE_SID=dw
rq=`date +"%Y%m%d" `
xx="start time":`date +"%Y%m%d%H%M" `
echo $xx>>/back/dwbak_$rq.log
rman log=/back/dwbak_$rq.log append <<EOF
connect target /;
run {
  allocate channel ch1 type disk;
  allocate channel ch2 type disk;
  allocate channel ch3 type disk;
  allocate channel ch4 type disk;
  allocate channel ch5 type disk;
  allocate channel ch6 type disk;
  allocate channel ch7 type disk;
  allocate channel ch8 type disk;
  allocate channel ch9 type disk;
  allocate channel ch10 type disk;
sql'alter system archive log current';
bakup AS COMPRESSED BACKUPSET database filesperset 10 format '/back/%DF_%T_%s_%p.bak';
sql'alter system archive log current';
  bakup AS COMPRESSED BACKUPSET archivelog all format '/back/%d_ARC_%T_%s_%p.bak';
backup current controlfile for standby format '/back/dw_ctrl_%T_%s';
  release channel ch1;
  release channel ch2;
  release channel ch3;
  release channel ch4;
  release channel ch5;
  release channel ch6;
  release channel ch7;
  release channel ch8;
  release channel ch9;
  release channel ch10;
}
EOF
xx="stop time":`date +"%Y%m%d%H%M" `
echo $xx>>/back/dwbak_$rq.log

开始备份：
nohup sh dw_bak.sh &

把备份得到的文件传送到备库

在备库上执行
恢复控制文件：
startup nomount

rman target /
restore standby controlfile from '/backup/传输过来的dw_ctrl_文件';

alter database mount;

创建恢复脚本：
dw_recover.sh

export ORACLE_SID=dw
rq=`date +"%Y%m%d" `
xx="start time":`date +"%Y%m%d%H%M" `
echo $xx>>/back/dwrecover_$rq.log
rman log=/back/dwrecover_$rq.log append <<EOF
connect target /;
run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
allocate channel ch4 type disk;
allocate channel ch5 type disk;
allocate channel ch6 type disk;
allocate channel ch7 type disk;
allocate channel ch8 type disk;
allocate channel ch9 type disk;
allocate channel ch10 type disk;
restore database;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;
release channel ch7;
release channel ch8;
release channel ch9;
release channel ch10;
}
EOF
xx="stop time":`date +"%Y%m%d%H%M" `
echo $xx>>/back/dwrecover_$rq.log

开始恢复：
nohup sh dw_recover.sh &

11. 启用复制
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

12.查看归档应用情况
检查归档应用情况：
SELECT /*+rule*/ ARCH.THREAD# "Thread",
ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD#, SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE (THREAD#, FIRST_CHANGE#) IN
(SELECT THREAD#, MAX(FIRST_CHANGE#)
FROM V$ARCHIVED_LOG
GROUP BY THREAD#)
GROUP BY THREAD#, SEQUENCE#) ARCH,
(SELECT THREAD#, SEQUENCE#
FROM V$LOG_HISTORY
WHERE (THREAD#, FIRST_CHANGE#) IN
(SELECT THREAD#, MAX(FIRST_CHANGE#)
FROM V$LOG_HISTORY
GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

13. 配置备库
create spfile='+DATA' from pfile;
vi initdw.ora
spfile='+data/dwadg/PARAMETERFILE/spfile.548.1086953287'

srvctl add database -d itsadg \
-r PHYSICAL_STANDBY -n its \
-o /oracle/app/oracle/product/11.2.0/db_1 \
-p +data/itsadg/PARAMETERFILE/spfile.548.1086953287

#srvctl add instance -d itsadg -i pmadg1 -n yxadg1
#srvctl add instance -d pmadg -i pmadg2 -n yxadg2
#srvctl add instance -d pmadg -i pmadg3 -n yxadg3

14、重启备库
sql>alter database recover managed standby database cancel;
shutdown immediate

$srvctl start -database -d dbname
sql>alter database recover managed standby database using current logfile disconnect from session;

15、检查集群状态
su - grid
crsctl status res -t

16、数据同步测试
可在主库创建一张表，插入几条数据，看备库是否能够查到该表和数据

斜风细雨时候

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ADG迁移

主库：77.12.73.11备库：77.12.73.271.主备库服务器配置服务名主库：su - oraclecd /oracle/app/oracle/product/11.2.0/db_1/network/adminvi tnsnames.oradw = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 77.12.73.11)(PORT = 1521)) (CONNECT_DATA = (SERVER...
复制链接

扫一扫