同构异机迁移方案2_目标服务器仅安装数据库软件scp物理文件

源端和目标端的数据库版本需要保持一致，补丁版本可以不一致，目标端磁盘空间不能小于源端空间，目标端只需要安装 Oracle 软件即可。

特别说明：本文档案例Oracle的安装路径不同，数据目录一致，采用scp的方式实现同构异机迁移。

迁移背景

	ip	os version	os user/password	db version	db user/password	数据量
源库	192.168.40.52	centos7.9 x86_64	root/dhh	11.2.0.4	sys/oracle	87G
目标库	192.168.40.152	centos7.9 x86_64	root/dhh	11.2.0.4	sys/oracle

由于源库服务器剩余的磁盘空间不是很多，采用scp的方式实现同构异机迁移，本文档案例Oracle的安装路径不同，数据目录一致。迁移步骤如下：

迁移前准备

源库

源库数据模拟

该步骤生产环境省略，纯属个人实验环境构建测试数据

--通过数据库主机连接
su - oracle
sqlplus / as sysdba
select sysdate from dual;

--操作系统上创建新的数据文件存放目录
[root@11g-db ~]# mkdir -p /data
[root@11g-db ~]# chown -R oracle:oinstall /data/

--创建表空间
--创建表空间，大小5m，开启自动扩展
create tablespace test datafile '/data/test01.dbf' size 5M autoextend on NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

--创建临时表空间
create temporary tablespace testtemp tempfile '/data/testtemp01.dbf'
size 50m autoextend on next 1m maxsize unlimited extent management local uniform size 4m;

##创建数据库用户
--创建用户，指定默认表空间和临时表空间
create user test identified by test default tablespace test temporary tablespace testtemp account unlock;
--赋予权限  最后的with admin option表示权限转授，可以把本身获得的权限再授权给其他用户
grant dba,connect,resource to test with admin option;
注意：密码最长30位
--切换用户
conn test/test

##创建表

create table test (id number primary key not null,name varchar2(100));
insert into test values (1,'hm');
insert into test values (2,'xk');
insert into test values (3,'whm');
commit;


create table t1 (id  int not null);
begin
  for i in 1..10000 loop
    insert into t1 values(dbms_random.value()*10000);
    --如果列是主键
    --insert into t1 values(i);
  end loop;
end;
/

确认源库信息服务器信息和物理文件位置

源库内存cpu磁盘信息

--内存
[oracle@shipin04 admin]$ free -g
              total        used        free      shared  buff/cache   available
Mem:            125          10           7          74         107          38
Swap:             0           0           0

--cpu
[oracle@shipin04 admin]$ lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                64
On-line CPU(s) list:   0-63
Thread(s) per core:    2
Core(s) per socket:    32
Socket(s):             1
NUMA node(s):          16
...

--磁盘
[oracle@shipin04 admin]$ df -Th
Filesystem                  Type      Size  Used Avail Use% Mounted on
/dev/vda3                   xfs       200G   71G  129G  36% /
devtmpfs                    devtmpfs   63G     0   63G   0% /dev
tmpfs                       tmpfs      63G     0   63G   0% /dev/shm
tmpfs                       tmpfs      63G  1.2G   62G   2% /run
tmpfs                       tmpfs      63G     0   63G   0% /sys/fs/cgroup
/dev/vda1                   xfs       197M  114M   84M  58% /boot
tmpfs                       tmpfs      13G     0   13G   0% /run/user/0
/dev/mapper/vg_vdb1-lv_vdb1 ext4      493G  413G   55G  89% /data
由于剩余的磁盘空间不是很多，采用scp的方式实现同构异机迁移

源库oracle软件目录位置

--查$ORACLE_BASE
[oracle@shipin04 ~]$ echo $ORACLE_BASE
/oracle/app

--查$ORACLE_HOME
[oracle@shipin04 ~]$ echo $ORACLE_HOME
/oracle/app/product/11.2.0/db_1

源库物理文件位置

su - oracle
export ORACLE_SID=shipin04
sqlplus / as sysdba

--查看表空间和数据文件位置
SQL> col tablespace_name for a50
SQL> col file_name for a70
SQL> set linesize 9999
SQL> select tablespace_name,file_name from dba_data_files order by 1;
TABLESPACE_NAME 				   FILE_NAME
-------------------------------------------------- --------------------------------------------------------
SYSAUX						   /oracle/app/oradata/shipin04/sysaux01.dbf
SYSTEM						   /oracle/app/oradata/shipin04/system01.dbf
SYXT						   /data/oracledbf/syxt01.dbf
SYXT						   /data/oracledbf/syxt03.dbf
SYXT						   /data/oracledbf/syxt02.dbf
TOPSP						   /data/oracledbf/topsp01.dbf
TOPSP						   /data/oracledbf/topsp02.dbf
UNDOTBS1					   /oracle/app/oradata/shipin04/undotbs01.dbf
USERS						   /data/oracledbf/users02.dbf
USERS						   /oracle/app/oradata/shipin04/users01.dbf

10 rows selected.


--查看控制文件：
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/app/oradata/shipin04/control01.ctl
/oracle/app/fast_recovery_area/shipin04/control02.ctl


--查看日志文件：
SQL> col member for a60
SQL> select group#,member,status from v$logfile order by 1;

    GROUP# MEMBER							STATUS
---------- ------------------------------------------------------------ -------
	 1 /oracle/app/oradata/shipin04/redo01.log
	 2 /oracle/app/oradata/shipin04/redo02.log
	 3 /oracle/app/oradata/shipin04/redo03.log
	 4 /oracle/app/oradata/shipin04/redo04.log
	 5 /oracle/app/oradata/shipin04/redo05.log
	 6 /oracle/app/oradata/shipin04/redo06.log

6 rows selected.

--参数文件
SQL> show parameter spfile;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 /oracle/app/product/11.2.0/db_
						 1/dbs/spfileshipin04.ora

--密码文件
$ORACLE_HOME/dbs 目录下
[oracle@shipin04 dbs]$ ls -l ora*
-rw-r----- 1 oracle oinstall 1536 Jul  8  2023 orapwshipin04

--监听文件
$ORACLE_HOME/network/admin
[oracle@shipin04 dbs]$ cd $ORACLE_HOME/network/admin
[oracle@shipin04 admin]$ ls -l
total 16
-rw-r--r-- 1 oracle oinstall 361 Apr 26  2023 listener.ora
drwxr-xr-x 2 oracle oinstall  64 Apr 26  2023 samples
-rw-r--r-- 1 oracle oinstall 381 Dec 17  2012 shrept.lst
-rw-r--r-- 1 oracle oinstall 211 Apr 26  2023 sqlnet.ora
-rw-r----- 1 oracle oinstall 327 Apr 26  2023 tnsnames.ora


[oracle@shipin04 ~]$ ls -lh /oracle/app/oradata/shipin04/
total 45G
-rw-r----- 1 oracle oinstall  13M Jan 31 10:11 control01.ctl
-rw-r----- 1 oracle oinstall 501M Jan 31 10:10 redo01.log
-rw-r----- 1 oracle oinstall 501M Jan 31 10:00 redo02.log
-rw-r----- 1 oracle oinstall 501M Jan 31 10:10 redo03.log
-rw-r----- 1 oracle oinstall 501M Jan 31 10:11 redo04.log
-rw-r----- 1 oracle oinstall 501M Jan 31 10:00 redo05.log
-rw-r----- 1 oracle oinstall 501M Jan 31 10:00 redo06.log
-rw-r----- 1 oracle oinstall 2.9G Jan 31 10:10 sysaux01.dbf
-rw-r----- 1 oracle oinstall 791M Jan 31 10:10 system01.dbf
-rw-r----- 1 oracle oinstall  42M Jan 30 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 6.2G Jan 31 10:10 undotbs01.dbf
-rw-r----- 1 oracle oinstall  32G Jan 31 10:11 users01.dbf

[oracle@shipin04 ~]$ ls -lh /data/oracledbf/
total 45G
-rwxrwxr-x 1 oracle oinstall 4.9G Jan 31 10:05 syxt01.dbf
-rwxrwxr-x 1 oracle oinstall 5.0G Jan 31 10:05 syxt02.dbf
-rwxrwxr-x 1 oracle oinstall 4.7G Jan 31 10:05 syxt03.dbf
-rwxrwxr-x 1 oracle oinstall  79M Jan 31 10:05 topsp01.dbf
-rwxrwxr-x 1 oracle oinstall 141M Jan 31 10:05 topsp02.dbf
-rw-r----- 1 oracle oinstall  30G Jan 31 10:08 users02.dbf

--查看归档模式
su - oracle
export ORACLE_SID=shipin04
sqlplus / as sysdba
SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /data/archivelogs
Oldest online log sequence     18130
Next log sequence to archive   18135
Current log sequence	       18135

冷备份是最简单的备份方式，但是也是恢复最快的方式。在工作中，冷备份也是会遇到的，这里简单的测试一下。

源库数据量统计

--查看数据量
select sum(bytes)/1024/1024/1024 as "size(G)" from dba_data_files;
   size(G)
----------
86.2625732

select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_data_files group by tablespace_name;
TABLESPACE_NAME 		       GB
------------------------------ ----------
UNDOTBS1		       6.10351563
SYSAUX			       2.87109375
USERS			       61.8518066
SYXT			       14.4514771
SYSTEM			       .771484375
TOPSP			       .213195801

6 rows selected.


select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
  from dba_segments
 where owner not in ('MDSYS', 'OUTLN', 'CTXSYS', 'OLAPSYS', 'HR', 'SYSTEM',
        'EXFSYS', 'SCOTT', 'DBSNMP', 'ORDSYS', 'SYSMAN', 'OE', 'PM', 'SH',
        'XDB', 'ORDDATA', 'IX', 'SYS', 'WMSYS') ;
ROUND(SUM(BYTES)/1024/1024/1024,2)||'G'
-----------------------------------------
67.33G


--查看用户
set lin1000 pagesize 999
select username,default_tablespace,temporary_tablespace from dba_users where username not in ('SYS','SYSTEM','HR','OUTLN','MGMT_VIEW','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','DBSNMP','WMSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','SCOTT','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR');

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
XZZSK1			       USERS			      TEMP
XZZSK			       USERS			      TEMP
WANGBIN 		       USERS			      TEMP
SYXK			       USERS			      TEMP

--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)
  FROM dba_objects d
 WHERE d.OWNER   in ('XZZSK1','XZZSK','WANGBIN','SYXK')
 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner) 
 GROUP BY D.OWNER
 ORDER BY D.OWNER ;
OWNER				 COUNT(1)
------------------------------ ----------
SYXK				     1770
XZZSK				      467

--每个业务用户下的各个对象类别的数量校验
SQL> select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects  where owner in ('SYXK','XZZSK')  group by OBJECT_TYPE,owner,status order by 1,3,2;

OWNER			       OBJECT_TYPE	   STATUS  COUNT(OBJECT_NAME)
------------------------------ ------------------- ------- ------------------
SYXK			       PROCEDURE	     INVALID		  1
SYXK			       VIEW		         INVALID		  1
SYXK			       DATABASE LINK   VALID		    3
SYXK			       INDEX		       VALID		  915
SYXK			       LOB		         VALID		   53
SYXK			       PROCEDURE	     VALID		    1
SYXK			       SEQUENCE 	     VALID		   12
SYXK			       TABLE		       VALID		  769
SYXK			       TRIGGER		     VALID		    2
SYXK			       VIEW		         VALID		   18
XZZSK			       DATABASE LINK   VALID		    5
XZZSK			       FUNCTION 	     VALID		    1
XZZSK			       INDEX		       VALID		   92
XZZSK			       LOB		         VALID		    5
XZZSK			       SEQUENCE 	     VALID		   18
XZZSK			       TABLE		       VALID		  346

16 rows selected.


--查询回收站lob对象数量(可选),lob数量不一致排查
select owner,type,count(*) from dba_recyclebin group by owner,type order by owner;
OWNER			       TYPE			   COUNT(*)
------------------------------ ------------------------- ----------
SYXK			       INDEX				  1
SYXK			       LOB			  	  5
SYXK			       LOB INDEX		  5
SYXK			       TABLE				 10


--数据量统计
----新增count_rows 函数
CREATE OR REPLACE FUNCTION count_rows (
	table_name IN varchar2, 
	owner IN varchar2 := NULL
)
RETURN number AUTHID current_user
AS
num_rows number;
	stmt varchar2(2000);
BEGIN
	IF owner IS NULL THEN
		stmt := 'select count(*) from "' || table_name || '"';
	ELSE
		stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';
	END IF;
	EXECUTE IMMEDIATE stmt INTO num_rows;
	RETURN num_rows;
END;
/
---调用count_rows 函数，统计各种表的记录数
select table_name, count_rows(table_name) nrows from user_tables  where tablespace_name='ENTSERVICE'
order by nrows desc;        

源库生成参数文件

由于源库和目标库的安装目录不一致，所以需生成源库的参数文件传输到目标库进行更改。

su - oracle
export ORACLE_SID=shipin04
sqlplus / as sysdba

--查看数据库实例名
SQL> show parameter name 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name		     string
db_file_name_convert		     string
db_name 			     string	 shipin04
db_unique_name			     string	 shipin04
global_names			     boolean	 FALSE
instance_name			     string	 shipin04
lock_name_space 		     string
log_file_name_convert		     string
processor_group_name		     string
service_names			     string	 shipin04

--生成pfile参数文件
SQL> create pfile='/home/oracle/pfile_shipin04_bak_20240131.ora' from spfile;
File created.

源库expdp全库备份(可选)

如果时间和磁盘空间允许，可先对源库进行expdp全库备份。本文档案例由于时间和磁盘空间紧张未做该步骤。

sqlplus / as sysdba
SQL>create directory dump_dir as '/oraDB/tmp_backup'
SQL>grant read,write on directory dump_dir to system;
expdp system/oracle directory=dump_dir dumpfile=backup.dmp full=y;

源库关库

--源库停止监听
su - oracle
[oracle@top130 ~]$ lsnrctl status
[oracle@top130 ~]$ lsnrctl stop
[oracle@top130 ~]$ lsnrctl stop

--源库杀用户连接 不建议 该步骤慎重 该文档未进行该操作
ps -ef|grep LOCAL=NO|awk '{print $2}'|xargs kill -9

--源库关库
su - oracle
export ORACLE_SID=shipin04
sqlplus / as sysdba
SQL> shutdown immediate

--查看进程，确认已关库
ps -ef | grep ora_pmon

目标库

目标库内存cpu磁盘信息

--内存
[oracle@syxk01:/home/oracle]$ free -g
              total        used        free      shared  buff/cache   available
Mem:            125          51          73           0           0          72
Swap:            15           0          15

--cpu
[oracle@syxk01:/home/oracle]$ lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                64
On-line CPU(s) list:   0-63
Thread(s) per core:    1
Core(s) per socket:    1
Socket(s):             64
NUMA node(s):          1
Vendor ID:             GenuineIntel
...

--磁盘
[oracle@syxk01:/home/oracle]$ df -Th
Filesystem          Type      Size  Used Avail Use% Mounted on
/dev/vda1           xfs       200G   32G  169G  16% /
devtmpfs            devtmpfs   63G     0   63G   0% /dev
tmpfs               tmpfs     126G     0  126G   0% /dev/shm
tmpfs               tmpfs      63G   17M   63G   1% /run
tmpfs               tmpfs      63G     0   63G   0% /sys/fs/cgroup
/dev/mapper/vg1-lv1 xfs       500G   33M  500G   1% /data
tmpfs               tmpfs      13G     0   13G   0% /run/user/54321
tmpfs               tmpfs      13G     0   13G   0% /run/user/0

由于源库和目标库内存cpu磁盘配置一样，因此后面参数文件不用更改内存参数，只用改路径就可以了。

安装数据库软件

采用脚本安装，该步骤省略。

--查$ORACLE_BASE
[oracle@syxk01:/home/oracle]$ echo $ORACLE_BASE
/oracle/app/oracle

--查$ORACLE_HOME
[oracle@syxk01:/home/oracle]$ echo $ORACLE_HOME
/oracle/app/oracle/product/11.2.0/db

扩展：静默安装数据库步骤

可参考如下：

创建用户

重建主机的Oracle用户 组 统一规范 uid gid 以保证共享存储挂接或其他需求的权限规范

groupadd -g 504 oinstall
groupadd -g 502 dba
useradd -g oinstall -G dba -u 502 oracle
passwd oracle

安装依赖包

安装好Oracle 需要的rpm包。安装rpm依赖包

rpm -q binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel glibc glibc-common glibc-devel gcc- gcc-c++ libaio-devel libaio libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel pdksh ksh
挂载本地yum源
yum install binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel glibc glibc-common glibc-devel gcc- gcc-c++ libaio-devel libaio libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel pdksh ksh

修改配置文件 /etc/security/limits.conf

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

修改配置文件/etc/sysctl.conf

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

重启主机或者输入命令 sysctl -p 生效当前配置

设置oracle 用户环境变量

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64
export ORACLE_SID=orcl  #根据具体的填写
#如果设置NLS_LANG，容易产生导入sql或dmp出错，因为其他环境下的不是utf8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8


ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
PATH=/usr/sbin:/usr/bin:/bin:/sbin:$ORACLE_HOME/bin export PATH

创建$ORACLE_BASE目录并赋予oracle目录权限：

mkdir -p /u01/app/oracle && chown -R oracle:oinstall /u01/app

上传数据库安装软件并用oracle 用户解压

静默安装

修改db_install.rsp文件参数

vi /home/oracle/database/response/db_install.rsp
####
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=DB_m2
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/home/app/oracle/oraInventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/home/app/oracle/product/11.2.0/db_1
ORACLE_BASE=/home/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oinstall
oracle.install.db.config.starterdb.characterSet=AL32UTF8
oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=/home/app/oracle/oradata
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=/home/app/oracle/recovery_data
DECLINE_SECURITY_UPDATES=true    //一定要设为true
####

安装oracle 软件

./runInstaller -responseFile /home/oracle/database/response/db_install.rsp -ignorePrereq -silent

安装监听

netca /silent /responsefile  /home/oracle/database/response/netca.rsp 

修改dbca配置文件

vi /home/oracle/database/response/dbca.rsp
###
RESPONSEFILE_VERSION = "11.2.0"  //不能更改
OPERATION_TYPE = "createDatabase"
GDBNAME = "orcl11gabc.us.oracle.com"  //全局数据库的名字=SID+主机域名
SID = "orcl11gabc"    //对应的实例名字
TEMPLATENAME = "General_Purpose.dbc" //建库用的模板文件
DATAFILEDESTINATION = /home/oracle/oradata //数据文件存放目录
RECOVERYAREADESTINATION=/home/oracle/recovery_data //恢复数据存放目录
CHARACTERSET = "AL32UTF8"   //字符集，重要!!! 建库后一般不能更改，所以建库前要确定清楚。
TOTALMEMORY = "5120"    //oracle内存5120MB
###

安装实例

dbca -silent -responseFile  /home/oracle/database/response/dbca.rsp

卸载oracle 软件

[oracle@db_m2 deinstall]$ pwd
/u01/app/oracle/product/11.2.0/db_1/deinstall
[oracle@db_m2 deinstall]$ ./deinstall

删实例

dbca -silent -deleteDatabase -sourceDB ORCL -sysDBAUserName sys -sysDBAPassword password

传输物理文件

传输参数文件

源库传输参数文件至目标库

将源库/home/oracle/pfile_shipin04_bak_20240131.ora拷贝到目标库的/home/oracle目录。

--源库上操作  传输参数文件
su - oracle
[oracle@shipin04 ~]$ scp -P 422 pfile_shipin04_bak_20240131.ora oracle@172.18.1.130:/home/oracle/

由于源库和目标库内存cpu磁盘配置一样，因此参数文件不用更改内存参数，只用改路径就可以了。

目标库更改参数文件

参数文件中

shipin04.__oracle_base='/oracle/app'#ORACLE_BASE set from environment 为$ORACLE_BASE目录

*.audit_file_dest='/oracle/app/admin/shipin04/adump' 为$ORACLE_BASE目录/admin/shipin04/adump

.control_files中control02.ctl路径为$ORACLE_BASE目录/fast_recovery_area/shipin04/control02.ctl

.db_recovery_file_dest为$ORACLE_BASE目录/fast_recovery_area

.diagnostic_dest为$ORACLE_BASE目录

--目标库操作  备份已传输的参数文件，避免改的有问题重新从源库服务器传输到目标库服务器
[oracle@syxk01:/home/oracle]$ cp pfile_shipin04_bak_20240131.ora pfile_shipin04_bak_20240131.ora_bak02

--更改参数文件中的oracle_base目录为目标库的oracle_base目录
su - oracle
vi pfile_shipin04_bak_20240131.ora
:%s#/oracle/app#/oracle/app/oracle#g
更改参数文件中的控制文件路径/oracle/app/fast_recovery_area/shipin04/control02.ctl为
参数文件中的
/oracle/app/oracle/fast_recovery_area/shipin04/control02.ctl

语法说明：
:%s#源目录#新目录#g

更改后记得确认，避免更改错误。

更改前

更改pfile_shipin04_bak_20240131.ora参数文件
su - oracle
vi pfile_shipin04_bak_20240131.ora
--更改前
shipin04.__db_cache_size=73819750400
shipin04.__java_pool_size=1879048192
shipin04.__large_pool_size=1073741824
shipin04.__oracle_base='/oracle/app'#ORACLE_BASE set from environment
shipin04.__pga_aggregate_target=21474836480
shipin04.__sga_target=85899345920
shipin04.__shared_io_pool_size=0
shipin04.__shared_pool_size=8053063680
shipin04.__streams_pool_size=536870912
*.audit_file_dest='/oracle/app/admin/shipin04/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/app/oradata/shipin04/control01.ctl','/oracle/app/fast_recovery_area/shipin04/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='shipin04'
*.db_recovery_file_dest='/oracle/app/fast_recovery_area'
*.db_recovery_file_dest_size=107374182400
*.diagnostic_dest='/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=shipin04XDB)'
*.filesystemio_options='SETALL'
*.log_archive_dest_1='location=/data/archivelogs'
*.open_cursors=300
*.pga_aggregate_target=21474836480
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=85899345920
*.sga_target=85899345920
*.undo_tablespace='UNDOTBS1'

更改后

shipin04.__db_cache_size=73819750400
shipin04.__java_pool_size=1879048192
shipin04.__large_pool_size=1073741824
shipin04.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
shipin04.__pga_aggregate_target=21474836480
shipin04.__sga_target=85899345920
shipin04.__shared_io_pool_size=0
shipin04.__shared_pool_size=8053063680
shipin04.__streams_pool_size=536870912
*.audit_file_dest='/oracle/app/oracle/admin/shipin04/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/app/oradata/shipin04/control01.ctl','/oracle/app/oracle/fast_recovery_area/shipin04/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='shipin04'
*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=107374182400
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=shipin04XDB)'
*.filesystemio_options='SETALL'
*.log_archive_dest_1='location=/data/archivelogs'
*.open_cursors=300
*.pga_aggregate_target=21474836480
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=85899345920
*.sga_target=85899345920
*.undo_tablespace='UNDOTBS1'

目标库创建参数文件中不存在的目录

目标库所在服务器上创建参数文件中涉及的不存在的目录，从上到下先查目录是否存在若不存在务必创建避免后面步骤报错。

--$ORACLE_BASE目录
[oracle@syxk01:/home/oracle]$ ls -ld /oracle/app/oracle
drwxrwxr-x 8 oracle oinstall 110 Jan 31 12:01 /oracle/app/oracle

--审计目录
[oracle@syxk01:/home/oracle]$ ls -ld /oracle/app/oracle/admin/shipin04/adump
ls: cannot access /oracle/app/oracle/admin/shipin04/adump: No such file or directory

[oracle@syxk01:/home/oracle]$ mkdir -p /oracle/app/oracle/admin/shipin04/adump

[oracle@syxk01:/home/oracle]$ ls -ld /oracle/app/oracle/admin/shipin04/adump
drwxr-xr-x 2 oracle oinstall 6 Jan 31 16:02 /oracle/app/oracle/admin/shipin04/adump

--数据目录
[oracle@syxk01:/home/oracle]$ ls -ld /oracle/app/oradata/shipin04/
ls: cannot access /oracle/app/oradata/shipin04/: No such file or directory

[oracle@syxk01:/home/oracle]$ mkdir -p /oracle/app/oradata/shipin04/

[oracle@syxk01:/home/oracle]$ ls -ld /oracle/app/oradata/shipin04/
drwxr-xr-x 2 oracle oinstall 6 Jan 31 16:03 /oracle/app/oradata/shipin04/

--控制文件目录
[oracle@syxk01:/home/oracle]$ ls -ld /oracle/app/oracle/fast_recovery_area/shipin04
ls: cannot access /oracle/app/oracle/fast_recovery_area/shipin04: No such file or directory

[oracle@syxk01:/home/oracle]$ mkdir -p /oracle/app/oracle/fast_recovery_area/shipin04

[oracle@syxk01:/home/oracle]$ ls -ld /oracle/app/oracle/fast_recovery_area/shipin04
drwxr-xr-x 2 oracle oinstall 6 Jan 31 16:03 /oracle/app/oracle/fast_recovery_area/shipin04

[oracle@syxk01:/home/oracle]$ ls -ld /oracle/app/oracle/fast_recovery_area
drwxr-x--- 3 oracle oinstall 22 Jan 31 16:03 /oracle/app/oracle/fast_recovery_area

--诊断目录
[oracle@syxk01:/home/oracle]$ ls -ld /oracle/app/oracle
drwxrwxr-x 8 oracle oinstall 110 Jan 31 12:01 /oracle/app/oracle

--归档目录
[oracle@syxk01:/home/oracle]$ ls -ld /data/archivelogs
ls: cannot access /data/archivelogs: No such file or directory

su - root
[root@syxk01:/root]$ mkdir -p /data/archivelogs
[root@syxk01:/root]$ chown -R oracle:oinstall /data/archivelogs/
[root@syxk01:/root]$ ls -ld /data/archivelogs/
drwxr-xr-x 2 oracle oinstall 6 Jan 31 16:04 /data/archivelogs/

目标库start nomount启库

su - oracle
[oracle@syxk01:/home/oracle]$ export ORACLE_SID=shipin04
[oracle@syxk01:/home/oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 31 16:08:16 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

idle 31-JAN-24> startup nomount pfile='/home/oracle/pfile_shipin04_bak_20240131.ora';
ORACLE instance started.

Total System Global Area 8.5516E+10 bytes
Fixed Size		    2262656 bytes
Variable Size		 1.1543E+10 bytes
Database Buffers	 7.3820E+10 bytes
Redo Buffers		  151117824 bytes
idle 31-JAN-24> 

目标库生成spfile参数文件(勿忘记)

该步骤不可省略，因为启库默认查找$ORACLE_HOME/dbs下的spfilesid.ora文件，如果不存在

SQL> create spfile from pfile='/home/oracle/pfile_shipin04_bak_20240131.ora';
SQL> shutdown immediate;
SQL> startup nomount;  --启动到nomount状态

传输控制文件

源库传输控制文件至目标库

源库控制文件位置：

/oracle/app/oradata/shipin04/control01.ctl

/oracle/app/fast_recovery_area/shipin04/control02.ctl

目标库控制文件位置：

/oracle/app/oradata/shipin04/control01.ctl

/oracle/app/oracle/fast_recovery_area/shipin04/control02.ctl

--源库操作 control01.ctl传输
su - oracle
cd /oracle/app/oradata/shipin04/
[oracle@shipin04 ~]$ scp -P 422 control01.ctl oracle@172.18.1.130:/oracle/app/oradata/shipin04/

--源库操作 control02.ctl传输
cd /oracle/app/fast_recovery_area/shipin04
[oracle@shipin04 ~]$ scp -P 422 control02.ctl oracle@172.18.1.130:/oracle/app/oracle/fast_recovery_area/shipin04/

目标库启库至mount状态

--目标库操作 启库到mount状态 
SQL> alter database mount;  --启动到mount状态

传输数据文件

源库数据文件位置：

/oracle/app/oradata/shipin04

/data/oracledbf

目标库数据文件位置：

本文档步骤采用目标库保持和源库数据文件位置一样，不存在的目录需提前创建。

目标库目录检查

目标库操作 检查数据文件所有目录是否存在，若不存在，创建不存在的数据目录。

--目标库操作 创建不存在的数据目录
[oracle@syxk01:/home/oracle]$ ls -ld /oracle/app/oradata/shipin04/
drwxr-xr-x 2 oracle oinstall 6 Jan 31 16:03 /oracle/app/oradata/shipin04/
[oracle@syxk01:/home/oracle]$ ls -ld /data/oracledbf
ls: cannot access /data/oracledbf: No such file or directory

[root@syxk01:/root]$ mkdir -p /data/oracledbf
[root@syxk01:/root]$ chown -R oracle:oinstall /data/oracledbf/
[root@syxk01:/root]$ ls -ld /data/oracledbf/
drwxr-xr-x 2 oracle oinstall 6 Jan 31 16:29 /data/oracledbf/

源库传输数据文件至目标库

--源库操作  传输数据文件
cd /oracle/app/oradata/shipin04/
[oracle@shipin04 shipin04]$ ls -l
total 46884852
-rw-r----- 1 oracle oinstall    13287424 Jan 31 18:05 control01.ctl
-rw-r----- 1 oracle oinstall   524288512 Jan 31 18:05 redo01.log
-rw-r----- 1 oracle oinstall   524288512 Jan 31 18:00 redo02.log
-rw-r----- 1 oracle oinstall   524288512 Jan 31 18:00 redo03.log
-rw-r----- 1 oracle oinstall   524288512 Jan 31 17:50 redo04.log
-rw-r----- 1 oracle oinstall   524288512 Jan 31 17:50 redo05.log
-rw-r----- 1 oracle oinstall   524288512 Jan 31 18:00 redo06.log
-rw-r----- 1 oracle oinstall  3082821632 Jan 31 18:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall   828383232 Jan 31 18:05 system01.dbf
-rw-r----- 1 oracle oinstall    42999808 Jan 30 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall  6553608192 Jan 31 18:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 34357911552 Jan 31 18:05 users01.dbf

[oracle@shipin04 ~]$ scp -P 422 *.dbf oracle@172.18.1.130:/oracle/app/oradata/shipin04/

[oracle@shipin04 shipin04]$ cd /data/oracledbf/
[oracle@shipin04 oracledbf]$ ls -l
total 46680776
-rwxrwxr-x 1 oracle oinstall  5241118720 Jan 31 18:05 syxt01.dbf
-rwxrwxr-x 1 oracle oinstall  5295316992 Jan 31 18:05 syxt02.dbf
-rwxrwxr-x 1 oracle oinstall  4980744192 Jan 31 18:05 syxt03.dbf
-rwxrwxr-x 1 oracle oinstall    82124800 Jan 31 18:05 topsp01.dbf
-rwxrwxr-x 1 oracle oinstall   146808832 Jan 31 18:05 topsp02.dbf
-rw-r----- 1 oracle oinstall 32054976512 Jan 31 18:05 users02.dbf

[oracle@shipin04 oracledbf]$ scp -P 422 *.dbf oracle@172.18.1.130:/data/oracledbf/

传输日志文件

源库日志文件位置：/oracle/app/oradata/shipin04/

目标库日志文件位置：/oracle/app/oradata/shipin04/

源库传输日志文件至目标库

--源库操作  传输数据文件
[oracle@shipin04 shipin04]$ ls -l
total 46884852
-rw-r----- 1 oracle oinstall    13287424 Jan 31 18:05 control01.ctl
-rw-r----- 1 oracle oinstall   524288512 Jan 31 18:05 redo01.log
-rw-r----- 1 oracle oinstall   524288512 Jan 31 18:00 redo02.log
-rw-r----- 1 oracle oinstall   524288512 Jan 31 18:00 redo03.log
-rw-r----- 1 oracle oinstall   524288512 Jan 31 17:50 redo04.log
-rw-r----- 1 oracle oinstall   524288512 Jan 31 17:50 redo05.log
-rw-r----- 1 oracle oinstall   524288512 Jan 31 18:00 redo06.log
-rw-r----- 1 oracle oinstall  3082821632 Jan 31 18:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall   828383232 Jan 31 18:05 system01.dbf
-rw-r----- 1 oracle oinstall    42999808 Jan 30 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall  6553608192 Jan 31 18:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 34357911552 Jan 31 18:05 users01.dbf


[oracle@shipin04 ~]$ scp -P 422 *.log oracle@172.18.1.130:/oracle/app/oradata/shipin04/

目标库启库至open

--目标库操作 启库到open状态 
SQL> alter database open;  --启动到mount状态

传输密码文件

源库传输密码文件至目标库

源库密码文件位置：$ORACLE_HOME/dbs 即/oracle/app/product/11.2.0/db_1/dbs

目标库密码文件位置：$ORACLE_HOME/dbs即/oracle/app/oracle/product/11.2.0/db/dbs

--源库操作  传输密码文件
[oracle@shipin04 dbs]$ ls -l
total 12996
-rw-rw---- 1 oracle oinstall     1544 Nov 13 15:55 hc_shipin04.dat
-rw-r--r-- 1 oracle oinstall     2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinstall       24 Apr 26  2023 lkSHIPIN04
-rw-r----- 1 oracle oinstall     1536 Jul  8  2023 orapwshipin04
-rw-r----- 1 oracle oinstall 13287424 Jan 31 00:42 snapcf_shipin04.f
-rw-r----- 1 oracle oinstall     3584 Dec 15 22:00 spfileshipin04.ora
[oracle@shipin04 dbs]$ pwd
/oracle/app/product/11.2.0/db_1/dbs

[oracle@shipin04 ~]$ scp -P 422 orapwshipin04 oracle@172.18.1.130:/oracle/app/oracle/product/11.2.0/db/dbs/

传输监听文件(可选)

如果监听文件没有特殊设置可省略该步骤。

本文档案例监听文件没有特殊设置，省略该步骤

目标库停止监听

--目标库操作  停止监听服务
su - oracle
lsnrctl stop
lsnrctl status

--目标库操作 监听文件备份
cd /oracle/app/oracle/product/11.2.0/db/network/admin
cp listener.ora listener.ora_old_bak_20240131

源库传输监听文件至目标库

源库监听文件位置：$ORACLE_HOME/dbs 即/oracle/app/product/11.2.0/db_1/network/admin

目标库监听文件位置：$ORACLE_HOME/dbs即/oracle/app/oracle/product/11.2.0/db/network/admin

--源库操作  传输监听文件
cd /oracle/app/product/11.2.0/db_1/network/admin
[oracle@shipin04 admin]$ ls -l
total 16
-rw-r--r-- 1 oracle oinstall 361 Apr 26  2023 listener.ora
drwxr-xr-x 2 oracle oinstall  64 Apr 26  2023 samples
-rw-r--r-- 1 oracle oinstall 381 Dec 17  2012 shrept.lst
-rw-r--r-- 1 oracle oinstall 211 Apr 26  2023 sqlnet.ora
-rw-r----- 1 oracle oinstall 327 Apr 26  2023 tnsnames.ora
[oracle@shipin04 admin]$ pwd
/oracle/app/product/11.2.0/db_1/network/admin

[oracle@shipin04 ~]$ scp -P 422 listener.ora oracle@172.18.1.130:/oracle/app/oracle/product/11.2.0/db/network/admin/

目标库启动监听

--目标库操作  启动监听服务
su - oracle
lsnrctl start
lsnrctl status

修改数据库监听配置

alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.141.0.14)(PORT=1521))';
alter system register;

修改新库主机名

#hostname shipin04
#vi /etc/sysconfig/network
#vi /etc/hosts

目标库数据量统计

目标库数据量统计
--查看数据量
select sum(bytes)/1024/1024/1024 as "size(G)" from dba_data_files;
   size(G)
----------
86.2625732

select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_data_files group by tablespace_name;
TABLESPACE_NAME 		       GB
------------------------------ ----------
UNDOTBS1		       6.10351563
SYSAUX			       2.87109375
USERS			       61.8518066
SYXT			       14.4514771
SYSTEM			       .771484375
TOPSP			       .213195801

6 rows selected.


select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
  from dba_segments
 where owner not in ('MDSYS', 'OUTLN', 'CTXSYS', 'OLAPSYS', 'HR', 'SYSTEM',
        'EXFSYS', 'SCOTT', 'DBSNMP', 'ORDSYS', 'SYSMAN', 'OE', 'PM', 'SH',
        'XDB', 'ORDDATA', 'IX', 'SYS', 'WMSYS') ;
ROUND(SUM(BYTES)/1024/1024/1024,2)||'G'
-----------------------------------------
67.33G


--查看用户
set lin1000 pagesize 999
select username,default_tablespace,temporary_tablespace from dba_users where username not in ('SYS','SYSTEM','HR','OUTLN','MGMT_VIEW','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','DBSNMP','WMSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','SCOTT','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR');

USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
XZZSK1			       USERS			      TEMP
XZZSK			       USERS			      TEMP
WANGBIN 		       USERS			      TEMP
SYXK			       USERS			      TEMP

--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)
  FROM dba_objects d
 WHERE d.OWNER   in ('XZZSK1','XZZSK','WANGBIN','SYXK')
 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner) 
 GROUP BY D.OWNER
 ORDER BY D.OWNER ;
OWNER				 COUNT(1)
------------------------------ ----------
SYXK				     1770
XZZSK				      467

--每个业务用户下的各个对象类别的数量校验
SQL> select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects  where owner in ('SYXK','XZZSK')  group by OBJECT_TYPE,owner,status order by 1,3,2;

OWNER			       OBJECT_TYPE	   STATUS  COUNT(OBJECT_NAME)
------------------------------ ------------------- ------- ------------------
SYXK			       PROCEDURE	     INVALID		  1
SYXK			       VIEW		         INVALID		  1
SYXK			       DATABASE LINK   VALID		    3
SYXK			       INDEX		       VALID		  915
SYXK			       LOB		         VALID		   53
SYXK			       PROCEDURE	     VALID		    1
SYXK			       SEQUENCE 	     VALID		   12
SYXK			       TABLE		       VALID		  769
SYXK			       TRIGGER		     VALID		    2
SYXK			       VIEW		         VALID		   18
XZZSK			       DATABASE LINK   VALID		    5
XZZSK			       FUNCTION 	     VALID		    1
XZZSK			       INDEX		       VALID		   92
XZZSK			       LOB		         VALID		    5
XZZSK			       SEQUENCE 	     VALID		   18
XZZSK			       TABLE		       VALID		  346

16 rows selected.


--查询回收站lob对象数量(可选),lob数量不一致排查
select owner,type,count(*) from dba_recyclebin group by owner,type order by owner;
OWNER			       TYPE			   COUNT(*)
------------------------------ ------------------------- ----------
SYXK			       INDEX				  1
SYXK			       LOB			  	  5
SYXK			       LOB INDEX		  5
SYXK			       TABLE				 10


--数据量统计
----新增count_rows 函数
CREATE OR REPLACE FUNCTION count_rows (
	table_name IN varchar2, 
	owner IN varchar2 := NULL
)
RETURN number AUTHID current_user
AS
num_rows number;
	stmt varchar2(2000);
BEGIN
	IF owner IS NULL THEN
		stmt := 'select count(*) from "' || table_name || '"';
	ELSE
		stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';
	END IF;
	EXECUTE IMMEDIATE stmt INTO num_rows;
	RETURN num_rows;
END;
/
---调用count_rows 函数，统计各种表的记录数
select table_name, count_rows(table_name) nrows from user_tables  where tablespace_name='ENTSERVICE'
order by nrows desc;        

Oracle 冷备份以及恢复——异机备份恢复 - 腾讯云开发者社区-腾讯云