因工作需要，要把数据库从服务器A迁到服务器B。
1.在服务器B搭建数据库环境
1.1 安装依赖包
yum install binutils -y
yum install compat-libcap1 -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install make -y
yum install nfs-utils -y
yum install net-tools -y
yum install smartmontools -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y
1.2创建oracle用户组和用户
# groupadd dba
# useradd -g dba oracle
# passwd oracle
# id oracle
1.3切换oracle用户配置bash_profile文件
# su – oracle
# vi .bash_profile
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=; export TMPDIR
ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=headdb; export ORACLE_UNQNAME
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=${ORACLE_BASE}/product/12.2.0.1/dbhome_1; export ORACLE_HOME
ORACLE_SID=headdb; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH export PATH
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK;export NLS_LANG
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
修改完保存并编译文件（ps：i进入编辑状态，esc进入命令状态，:wq保存并推出，:q不保存退出，:q!有修改的强制不保存退出）
# source ~/.bash_profile
1.4下载并上传oracle安装包到服务器B
oracle12c下载地址：https://www.oracle.com/database/technologies/oracle-database-software-downloads.html
使用root用户解压并赋权
# chown -R oracle:dba /home/oracle/database
# chmod -R 755 /home/oracle /database
1.5修改oracle安装响应文件db_install.rsp
#-------------------------------------------------------------------------------
# Do not change the following system generated value.
#-------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_sch
ema_v12.2.0
#-------------------------------------------------------------------------------
# Specify the installation option.
# It can be one of the following:
# - INSTALL_DB_SWONLY
# - INSTALL_DB_AND_CONFIG
# - UPGRADE_DB
#-------------------------------------------------------------------------------
oracle.install.option=INSTALL_DB_SWONLY
#-------------------------------------------------------------------------------
# Specify the Unix group to be set for the inventory directory.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=dba
#-------------------------------------------------------------------------------
# Specify the location which holds the inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/opt/oracle/oraInventory
#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Home.
#-------------------------------------------------------------------------------
ORACLE_HOME=/opt/oracle/product/12.2.0.1/dbhome_1
#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Base.
#-------------------------------------------------------------------------------
ORACLE_BASE=/opt/oracle
#-------------------------------------------------------------------------------
# Specify the installation edition of the component.
#
# The value should contain only one of these choices.
# - EE : Enterprise Edition
# - SE2 : Standard Edition 2
#-------------------------------------------------------------------------------
oracle.install.db.InstallEdition=EE
###############################################################################
# #
# PRIVILEGED OPERATING SYSTEM GROUPS #
# ------------------------------------------ #
# Provide values for the OS groups to which SYSDBA and SYSOPER privileges #
# needs to be granted. If the install is being performed as a member of the #
# group "dba", then that will be used unless specified otherwise below. #
# #
# The value to be specified for OSDBA and OSOPER group is only for UNIX based #
# Operating System. #
# #
###############################################################################
#------------------------------------------------------------------------------
# The OSDBA_GROUP is the OS group which is to be granted SYSDBA privileges.
#-------------------------------------------------------------------------------
oracle.install.db.OSDBA_GROUP=dba
#------------------------------------------------------------------------------
# The OSOPER_GROUP is the OS group which is to be granted SYSOPER privileges.
# The value to be specified for OSOPER group is optional.
#------------------------------------------------------------------------------
oracle.install.db.OSOPER_GROUP=dba
#------------------------------------------------------------------------------
# The OSBACKUPDBA_GROUP is the OS group which is to be granted SYSBACKUP privile
ges.
#------------------------------------------------------------------------------
oracle.install.db.OSBACKUPDBA_GROUP=dba
#------------------------------------------------------------------------------
# The OSDGDBA_GROUP is the OS group which is to be granted SYSDG privileges.
#------------------------------------------------------------------------------
oracle.install.db.OSDGDBA_GROUP=dba
#------------------------------------------------------------------------------
# The OSKMDBA_GROUP is the OS group which is to be granted SYSKM privileges.
#------------------------------------------------------------------------------
oracle.install.db.OSKMDBA_GROUP=dba
#------------------------------------------------------------------------------
# The OSRACDBA_GROUP is the OS group which is to be granted SYSRAC privileges.
#------------------------------------------------------------------------------
oracle.install.db.OSRACDBA_GROUP=dba
保存并退出
1.6转到database目录执行安装命令
# ./runInstaller -ignoreSysPrereqs -ignorePrereq -waitforcompletion \ -showProgress -silent -responseFile /home/oracle/database/response/db_install.rsp
安装完切换root用户执行命令
# /opt/oracle/oraInventory/orainstRoot.sh
# /opt/oracle/root.sh
切换到oracle用户
# su - oracle
# sqlplus / as sysdba
数据库安装成功
2.数据库迁移
2.1找到服务器A的pfile、control_files和数据库实例路径
show parameter pfile
show parameter control_files
select name from v$datafile union all
select name from v$tempfile union all
select member from v$logfile;
2.2创建pfile
create pfile from spfile;
2.3shutdown服务器A的数据库
shutdown immediate
2.4把数据库实例文件迁移到服务器B
2.5把服务器A生成的pfile移到服务器并修改路径为服务器B的数据库路径
headdb.__data_transfer_cache_size=0
headdb.__db_cache_size=1845493760
headdb.__inmemory_ext_roarea=0
headdb.__inmemory_ext_rwarea=0
headdb.__java_pool_size=16777216
headdb.__large_pool_size=33554432
headdb.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
headdb.__pga_aggregate_target=838860800
headdb.__sga_target=2516582400
headdb.__shared_io_pool_size=117440512
headdb.__shared_pool_size=486539264
headdb.__streams_pool_size=0
*._fix_control='11814428:0'
*._optimizer_cartesian_enabled=FALSE
*._optimizer_dsdir_usage_control=0
*._smm_auto_cost_enabled=FALSE
*._swrf_mmon_dbfus=FALSE
*.audit_file_dest='/opt/oracle/admin/headdb/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/home/oracleIns/oradata/headdb/control01.ctl','/home/oracleIns/oradata/headdb/control02.ctl'
*.db_block_size=8192
*.db_name='headdb'
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=headdb)'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.parallel_degree_policy='MANUAL'
*.pga_aggregate_target=798m
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2394m
*.undo_tablespace='UNDOTBS1'
保存并退出
注：service 要改成新库的名称；audit_file_dest目录不存在要mkdir
注2：新迁移过来的文件要用root用户把权限赋给oracle用户
2.6登录oracle用户，连接sqlplus
startup nomount pfile='$ORACLE_HOME/dbs/initheaddb.ora'
alter database mount;
2.7查询数据文件、日志文件、临时文件路径并修改为新服务器路径
select name from v$datafile union all
select name from v$tempfile union all
select member from v$logfile;
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/system01.dbf' to '/home/oracleIns/oradata/headdb/system01.dbf'
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/sysaux01.dbf' to '/home/oracleIns/oradata/headdb/sysaux01.dbf';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/undotbs01.dbf' to '/home/oracleIns/oradata/headdb/undotbs01.dbf';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/TS_wolf.dbf' to '/home/oracleIns/oradata/headdb/TS_name.dbf';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/users01.dbf' to '/home/oracleIns/oradata/headdb/users01.dbf';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/temp01.dbf' to '/home/oracleIns/oradata/headdb/temp01.dbf';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/redo03.log' to '/home/oracleIns/oradata/headdb/redo03.log';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/redo02.log' to '/home/oracleIns/oradata/headdb/redo02.log';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/redo01.log' to '/home/oracleIns/oradata/headdb/redo01.log';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/redo04.log' to '/home/oracleIns/oradata/headdb/redo04.log';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/redo05.log' to '/home/oracleIns/oradata/headdb/redo05.log';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/redo06.log' to '/home/oracleIns/oradata/headdb/redo06.log';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/redo07.log' to '/home/oracleIns/oradata/headdb/redo07.log';
alter database rename file '/u01/app/oracle/product/12.2.0/oradata/headdb/redo08.log' to '/home/oracleIns/oradata/headdb/redo08.log';
打开数据库
alter database open;
用pfile创建spfile
create spfile from pfile;
2.8配置监听文件
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = headdb)
(ORACLE_HOME = /opt/oracle/product/12.2.0.1/dbhome_1)
(SID_NAME = headdb)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
2.9打开监听
lsnrctl start
如果状态为unknow，连接sql，执行注册
alter system register;
退出查看监听状态lsnrctl status，若为ready则监听启动成功
至此数据库迁移成功
1427
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
