oracle停库迁移

1、查看两端数据库版本
select * from product_component_version
lsnrctl status

2、查看字符编码
select * from nls_database_parameters where parameter in (‘NLS_LANGUAGE’,‘NLS_TERRITORY’,‘NLS_CHARACTERSET’);

2、查看目标端机器大小
df -h

3、查看源机器文件位置
su - oracle
export ORACLE_SID=xxx
sqlplus / as sysdba

select name from v$datafileunionallselectnamefromv$controlfile union all
select name from v$tempfileunionallselectmemberfromv$logfile;
确定好位置后查看目标端的数据库对应位置是否有足够空间，注意是不是/下有足够空间
查看文件大小
ll -thr --block-size=1k 文件明 | awk ‘{print $5}’ | xargs | sed ‘s/ /+/g’ | bc -l

4、目标端创建对应目录，并修改属主属组，注意目标端是否有文件 千万别覆盖了

mkdir -p
chown oracle:oinstall -R /u02/

5、登录源机器，关闭数据库以及监听（如果有两个数据库实例就不要关闭监听）
sqlplus / as sysdba
show parameter name
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
shutdown
shut immediate;
关闭数据库、监听
lsnrctl stop

6、免密（A免密登录B）
查看目标机器和源机器的oracle家目录下是否有.ssh文件夹
两端都没有的话执行ssh-keygen
进入A .ssh目录，复制其公钥粘贴到B的.ssh/authorized_keys（需要自己创建），并修改权限chmod 600 authorized_keys

7、传数据
参数文件
cd $ORACLE_HOME/dbs/ spfileoralilk.ora orapworalilk
scp $ORACL{E}_{H}OME/dbs/spfileoralilk.oraoracle@10.199.12.12:$ORACLE_HOME/dbs/spfileoralilk.ora
scp $ORACL{E}_{H}OME/dbs/orapworalilkoracle@10.199.12.12:$ORACLE_HOME/dbs/orapworalilk

写脚本，传输其他文件
select ‘scp ‘||name||’ oracle@ip:’||name as name from (
select name from v$datafileunionallselectnamefromv$controlfile union all
select name from v$tempfileunionallselectmemberfromv$logfile );

vim scp.sh
nohup bash scp_oracle_data.sh &

8、传完数据，检查大小
ll -thr --block-size=1k 文件明 | awk ‘{print $5}’ | xargs | sed ‘s/ /+/g’ | bc -l
yum install -y bc

9、目标机器切换至oracle用户
su - oracle
export ORACLE_SID=oralilk
sqlplus / as sysdba

7、启动机器
startup
报错

8、启动监听
lsrctl start

9、进入数据库
sqlplus / as sysdba
alter system register;
启动失败
startup

10、查看日志
ll $ORACL{E}_{H}OME/dbs/spfile$ORACLE_SID.ora
strings $ORACL{E}_{H}OME/dbs/spfile$ORACLE_SID.ora

[oracle@hz-bd-release-oracle-199-161-47 ~]$
[oracle@hz-bd-release-oracle-199-161-47 ~]$ ll $ORACL{E}_{H}OME/dbs/spfile$ORACLE_SID.ora
-rw-r----- 1 oracle oinstall 3584 Jun 24 17:05 /u01/oracle/product/11.2.0/dbs/spfileoralilk.ora

[oracle@hz-bd-release-oracle-199-161-47 ~]$ strings /u01/oracle/product/11.2.0/dbs/spfileoralilk.ora
oralilk.__db_cache_size=1560281088
oralilk.__java_pool_size=16777216
oralilk.__large_pool_size=33554432
oralilk.__oracle_base=‘/u01/oracle’#ORACLE_BASE set from environment
oralilk.__pga_aggregate_target=1073741824
oralilk.__sga_target=2147483648
oralilk.__shared_io_pool_size=0
oralilk.__shared_pool_size=452984832
oralilk.__streams_pool_size=0
*._external_scn_rejection_threshold_hours=4
*._index_partition_large_extents=‘FALSE’
*._optimizer_mjc_enabled=FALSE
*._optimizer_sortmerge_joi
n_enabled=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents=‘FALSE’
*._serial_direct_read=‘never’
*.audit_file_dest=‘/u01/oracle/admin/oralilk/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.4’
*.control_files=‘/u02/pridb/oralilk/data/control01.ctl’,‘/u02/pridb/oralilk/data/control02.ctl’
*.db_block_size=8192
*.db_domain=‘’
*.db_name=‘oralilk’
*.db_recovery_file_dest=‘/u02/pridb/oralilk/flash’
*.db_recovery_file_dest_size=4322230272
*.deferred_segment_creation=FALSE
iagnostic_dest=‘/u01/oracle’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=oralilkXDB)’
*.local_listener=‘’
*.log_buffer=67108864
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=2000
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=200
*.sga_target=2147483648
*.undo_tablespace=‘UNDOTBS1’

创建相应位置
[oracle@hz-bd-release-oracle-199-161-47 ~]$ #mkdir -p /u01/oracle/admin/oralilk/adump /u02/pridb/oralilk/data/ /u02/pridb/oralilk/data/ /u02/pridb/oralilk/flash
基本上复制这些文件
.audit_file_dest=‘/u01/oracle/admin/oralilk/adump
*.control_files=’/u02/pridb/oralilk/data/control01.ctl’,‘/u02/pridb/oralilk/data/control02.ctl’
*.db_recovery_file_dest=‘/u02/pridb/oralilk/flash’
iagnostic_dest=‘/u01/oracle’

11、启动机器
startup

12、创建测试用户
grant connect,resource to xx identified by 123;

13、
sqlplus xx/123@ip:1521/yyck

14、查看监听状态
lsnrctl stauts 查看监听状态