Oracle部署以及数据迁移

Posted on Edited on In Word count in article: 9.6k Reading time ≈ 9 mins.

本文章曾多次修改, 截图或者代码提及到的相关主机名和ip以及目录可能存在变化, 请勿信以为真.

一、安装前准备

1.1、上传文件

scp linux.x64_11gR2_database_1of2 [email protected]:/data/oracle
scp linux.x64_11gR2_database_2of2 [email protected]:/data/oracle

1.2、解压文件

unzip \*.zip

1.3、安装依赖

yum install gcc make binutils gcc-c++ compat-libstdc++-33elfutils-libelf-devel elfutils-libelf-devel-static ksh libaio libaio-develnumactl-devel sysstat unixODBC unixODBC-devel pcre-devel –y

1.4、 添加主机名到hosts文件

sed -i "1s/$/ $(hostname)/" /etc/hosts

1.5、添加安装用户和用户组

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle

1.6、修改内核参数

编辑文件: vim /etc/sysctl.conf
在文件末尾添加如下:

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 1073741824
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

立即生效: sysctl -p

1.7、修改用户的限制文件

编辑文件: vim /etc/security/limits.conf
在文件末尾添加:

oracle           soft    nproc           2047
oracle           hard    nproc           16384
oracle           soft    nofile          1024
oracle           hard    nofile         65536
oracle           soft    stack           10240

编辑文件: vim /etc/pam.d/login
在文件末尾添加:

session required  /lib64/security/pam_limits.so
session required   pam_limits.so

编辑文件: vim /etc/profile
在文件末尾添加:

if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
      ulimit -p 16384
      ulimit -n 65536
  else
      ulimit -u 16384 -n 65536
  fi
fi

1.8、安装目录以及权限设置

mkdir -p /data/oracle/product/11.2.0
mkdir /data/oracle/oradata
mkdir /data/oracle/inventory
mkdir /data/oracle/fast_recovery_area
chown -R oracle:oinstall /data/oracle
chmod -R 775 /data/oracle

1.9、Oracle用户环境变量

切换到Oracle下: su -l oracle

编辑文件: vim .bash_profile
在文件末尾添加:

ORACLE_BASE=/data/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0
ORACLE_SID=orcl
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

注意: 数据库实例要一致
ORACLE_SID=orcl

二、安装与配置

2.1、以静默方式安装

编辑配置文件: vim /data/database/response/db_install.rsp
注意主机名请按照实际来写: ORACLE_HOSTNAME=CentOS

oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=CentOS
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/data/oracle/inventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/data/oracle/product/11.2.0
ORACLE_BASE=/data/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
DECLINE_SECURITY_UPDATES=true

执行静默安装: ./runInstaller -silent -responseFile /data/database/response/db_install.rsp -ignorePrereq
image.png

看到如下信息表示安装成功
新打开一个终端, 以root用户执行下面命令来完成安装: 

sh /data/oracle/inventory/orainstRoot.sh
sh /data/oracle/product/11.2.0/root.sh

image.png

2.2、以静默方式配置监听

切换成oracle用户,执行下面命令:

配置文件: vim /data/database/response/netca.rsp 一般没什么要修改的地方
注意: /silent 不是 -silent /responseFile 不是-responseFilet

执行静默监听: netca /silent /responseFile /data/database/response/netca.rsp
image.png

2.3、以静默方式创建一个实例

切换成oracle用户,执行下面命令:

编辑配置文件: vim /data/database/response/dbca.rsp

RESPONSEFILE_VERSION = "11.2.0"
OPERATION_TYPE = "createDatabase"
GDBNAME = "orcl.test"
SID = "orcl"
TEMPLATENAME = "General_Purpose.dbc"
SYSPASSWORD = "123456"
SYSTEMPASSWORD = "123456"
SYSMANPASSWORD = "123456"
DBSNMPPASSWORD = "123456"
DATAFILEDESTINATION =/data/oracle/oradata
RECOVERYAREADESTINATION = /data/oracle/fast_recovery_area
CHARACTERSET = "ZHS16GBK"
# oracle内存1638MB,物理内存2G*80%
TOTALMEMORY = "1638"

dbca -silent -responseFile /data/database/response/dbca.rsp
image.png

2.4、启动oracle

lsnrctl start
lsnrctl status

三、修改端口

切换成oracle用户, 执行下面命令:

停止监听: lsnrctl stop
修改监听配置文件: vim /<path to your oracle home>/network/admin/listener.ora

1521 --> 11521 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC11521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = <your server host name>)(PORT = 11521))
    )
  )

ADR_BASE_LISTENER = /data/oracle

修改local_listener参数

$ sqlplus / as sysdba
SQL> show parameter local_listener
SQL> alter system set local_listener="(address = (protocol = tcp)(host = localhost)(port = 11521))";
SQL> show parameter local_listener

image.png
重启监听: lsnrctl reload

四、数据导入导出

4.1 创建逻辑目录

该命令不会在操作系统创建真正的目录（请先创建真正的目录），最好以system等管理员创建逻辑目录。

shell> sqlplus / as sysdba
SQL> create directory data_dir as '/data/backup';
Directory created.

在操作系统创建真实目录并修改权限
mkdir -p /data/backup
chown -R oracle:oinstall /data/backup

4.2 管理员目录

SQL>select * from dba_directories;

4.3 给system用户赋予’data_dir’的读写权限

SQL>grant read,write on directory data_dir to system;

五、 使用expdp导出数据

5.1 按用户导出

expdp system/password@orcl schemas=system dumpfile=expdp.dmp directory=data_dir

5.2 按表导出

expdp system/password@orcl tables=emp,dept dumpfile=expdp.dmp directory=data_dir

5.3 按查询条件导出

expdp system/password@orcl directory=data_dir dumpfile=expdp.dmp tables=empquery='where deptno=20'

5.4 按表空间导出

expdp system/password@orcl directory=data_dir dumpfile=tablespace.dmptablespaces=temp,example

5.5 整库导出

expdp system/password@orcl directory=data_dir dumpfile=full.dmp full=y

5.6 例子

全库导出实例:
expdp system/[email protected]/orcl dumpfile=all.dmp directory=data_dir full=y logfile=all.log

[oracle@centos7 backup]$ expdp system/[email protected]/orcl dumpfile=all.dmp directory=data_dir full=y logfile=all.log
Export: Release 11.2.0.4.0 - Production on Wed Apr 12 14:46:19 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/********@172.21.33.23/orcl dumpfile=all.dmp directory=data_dir full=y logfile=all.log 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.104 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
. . exported "RSY"."DATAEX_PROVIDE_APPLY_copy1"          114.3 MB  643767 rows
. . exported "DBSHUMENG"."PER_ASSI_FAMILY_BUSY_HOSPITAL"  70.17 MB  309988 rows

.
.   数据已脱敏, 中间省略 n 行
.

. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
. . exported "TDUSER"."TEST"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /data/backup/all.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Wed Apr 12 14:49:08 2023 elapsed 0 00:02:47

六、 使用impdp导出数据

6.1 导入用户

从用户scott导入到用户scott
impdp scott/tiger@orcl directory=data_dir dumpfile=expdp.dmp schemas=scott

6.2 导入表

从scott用户中把表dept和emp导入到system用户中
impdp system/manager@orcl directory=data_dir dumpfile=expdp.dmp tables=scott.dept,scott.emp remap_schema=scott:system

6.3 导入表空间

impdp system/manager@orcl directory=data_dir dumpfile=tablespace.dmp tablespaces=example

6.4 导入数据库(全)

impdb system/manager@orcl directory=data_dir dumpfile=full.dmp full=y

6.5 追加数据

impdp system/manager@orcl directory=data_dir dumpfile=expdp.dmp schemas=systemtable_exists_action

6.6 例子

数据库(全)导入实例:
impdp system/[email protected]:11521/orcl.test directory=data_dir dumpfile=all.dmp logfile=import.log full=y ignore=y


[oracle@host backup]$impdp system/[email protected]:11521/orcl.test directory=data_dir dumpfile=all.dmp logfile=import.log full=y ignore=y

Import: Release 11.2.0.1.0 - Production on Wed Apr 12 22:01:24 2023
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
;;; Legacy Mode Active due to the following parameters:
;;; Legacy Mode Parameter: "ignore=TRUE" Location: Command Line, Replaced with: "table_exists_action=append"
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@172.19.3.12:11521/orcl.test directory=data_dir dumpfile=all.dmp logfile=import.log full=y table
_exists_action=append 
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
ORA-31684: Object type TABLESPACE:"TRANSFERDB" already exists
Processing object type DATABASE_EXPORT/PROFILE
ORA-31684: Object type PROFILE:"MONITORING_PROFILE" already exists
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
.
. 数据已脱敏, 中间省略 n 行
.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 7644 error(s) at 22:17:04

七、 数据导入导出常见错误

记录一下一次迁移Oracle中遇到的问题

7.1 全库数据导入不完整

Oracle A:
所属: 生产环境 172.21.33.23
版本: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Oracle B:
所属: 测试环境 172.19.3.12
版本: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

版本有细微差别, 但是影响不大

从Oracle A 导出全库顺利, 导入Oracle B 只能导入部分数据

相关报错: ORA-01119: error in creating database file '/data/app/oracle/oradata/transferdb/transferdb.dbf'

Oracle A 的 transferdb 表空间文件–>'/data/app/oracle/oradata/transferdb/transferdb.dbf'
然而在 Oracle B中连 '/data/app/' 这个目录都没有, 所以会创建表空间失败.

解决: 由于两个Oracle不是同一个人安装的, 导致实际Oracle的目录存在差别。
虽然impdp在导入数据的时候会创建原本不存在的表空间, 由于两个Oracle表空间所属目录不同导致创建表空间失败, 手动创建表空间后再导入数据成功.

参考:
Linux安装Oracle11g
Oracle使用EXPDP和IMPDP详细说明