Oracle 冷迁库
操作步骤：
1.查看当前数据库的数据文件，临时文件，日志文件，控制文件，参数文件等信息。
2.根据当前spfile创建pfile文件,正常关闭数据库,移动源数据库文件到新的存储路径。
3.修改数据库参数文件,更改控制文件路径为新的存储路径,用改好的pfile文件启动数据库到mount状态。
4.重定向数据库的所有数据文件、日志文件路径，然后正常打开数据库。
5.核查各文件路径没有问题，根据当前pfile创建spfile，重启数据库实例。
1.查看当前数据库的数据文件，临时文件，日志文件，控制文件，参数文件等信息。
参考地址：https://www.cnblogs.com/jyzhao/p/3968504.html
- 查看当前数据库的数据文件，临时文件，日志文件，控制文件，参数文件等信息。
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/data/zmmis/mdm/mdm/system01.dbf
/data/zmmis/mdm/mdm/sysaux01.dbf
/data/zmmis/mdm/mdm/undotbs01.dbf
/data/zmmis/mdm/mdm/users01.dbf
/data/zmmis/mdm/MDM_DAT.DBF
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/data/zmmis/mdm/mdm/temp01.dbf
/data/zmmis/mdm/MDM_TEMP.DBF
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/data/zmmis/mdm/mdm/redo01.log
/data/zmmis/mdm/mdm/redo02.log
/data/zmmis/mdm/mdm/redo03.log
/data/zmmis/mdm/mdm/redo04.log
/data/zmmis/mdm/mdm/redo05.log
/data/zmmis/mdm/mdm/redo06.log
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/data/zmmis/mdm/mdm/control01.ctl
/u01/app/oracle/flash_recovery_area/mdm/control02.ctl
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilemdm.ora
2.根据当前spfile创建pfile文件,正常关闭数据库,移动源数据库文件到新的存储路径。
SQL> create pfile from spfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
- 在新服务器创建目录，并分配所有者为oracle:oinstall，然后把旧服务器的数据库文件复制到新服务器创建好的目录里。
- 复制监听文件到新服务器Oracle目录里。
- 修改数据库参数文件,更改控制文件路径为新的存储路径,并且把改好的pfile文件复制到新服务器目录里（请务必和旧服务器路径一致）
[oracle@zmyqsj1 pfile]$ vi /u01/app/oracle/admin/mdm/pfile/init.ora.529201516725
jingyu.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/opt/app/oracle/admin/jingyu/adump'
*.control_files='/oradata/sysdata/jingyu/control01.ctl','/opt/app/oracle/fast_recovery_area/jingyu/control02.ctl'
*.db_recovery_file_dest='/opt/app/oracle/fast_recovery_area'
[oracle@zmyqsj1 dbs]$ export ORACLE_SID=mdm
[oracle@zmyqsj1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Feb 17 14:27:02 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup PFILE='/u01/app/oracle/admin/mdm/pfile/init.ora.529201516725' nomount;
ORACLE instance started.
Total System Global Area 1.0957E+10 bytes
Fixed Size 2216504 bytes
Variable Size 5838474696 bytes
Database Buffers 5100273664 bytes
Redo Buffers 15745024 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00214: control file '/u01/app/oracle/flash_recovery_area/mdm/control02.ctl'
version 764651 inconsistent with file '/bak/dbf/mdm/mdm/control01.ctl' version
764553
提示版本不一致，因为有2个控制文件，删除掉control02.ctl试试
vi /u01/app/oracle/product/11.2.0/db_1/dbs/spfilemdm.ora
删除掉/u01/app/oracle/flash_recovery_area/mdm/control02.ctl
[oracle@zmyqsj1 pfile]$ vi /u01/app/oracle/admin/mdm/pfile/init.ora.529201516725
删除掉/u01/app/oracle/flash_recovery_area/mdm/control02.ctl
SQL> alter database mount;
Database altered.
6、修改数据库的所有数据文件、日志文件路径，然后正常打开数据库。
SQL> alter database rename file '/data/zmmis/mdm/mdm/system01.dbf' to '/bak/dbf/mdm/mdm/system01.dbf';
Database altered.
SQL> alter database rename file '/data/zmmis/mdm/mdm/sysaux01.dbf' to '/bak/dbf/mdm/mdm/sysaux01.dbf';
Database altered.
SQL> alter database rename file '/data/zmmis/mdm/mdm/undotbs01.dbf' to '/bak/dbf/mdm/mdm/undotbs01.dbf';
Database altered.
SQL> alter database rename file '/data/zmmis/mdm/mdm/users01.dbf' to '/bak/dbf/mdm/mdm/users01.dbf';
Database altered.
SQL> alter database rename file '/data/zmmis/mdm/MDM_DAT.DBF' to '/bak/dbf/mdm/MDM_DAT.DBF';
Database altered.
SQL> alter database rename file '/data/zmmis/mdm/mdm/temp01.dbf' to '/bak/dbf/mdm/mdm/temp01.dbf';
Database altered.
SQL> alter database rename file '/data/zmmis/mdm/MDM_TEMP.DBF' to '/bak/dbf/mdm/MDM_TEMP.DBF';
Database altered.
SQL> alter database rename file '/data/zmmis/mdm/mdm/redo01.log' to '/bak/dbf/mdm/mdm/redo01.log';
Database altered.
already part of database
SQL> alter database rename file '/data/zmmis/mdm/mdm/redo02.log' to '/bak/dbf/mdm/mdm/redo02.log';
Database altered.
SQL> alter database rename file '/data/zmmis/mdm/mdm/redo03.log' to '/bak/dbf/mdm/mdm/redo03.log';
Databse altered.
SQL> alter database rename file '/data/zmmis/mdm/mdm/redo04.log' to '/bak/dbf/mdm/mdm/redo04.log';
Database altered.
SQL> alter database rename file '/data/zmmis/mdm/mdm/redo05.log' to '/bak/dbf/mdm/mdm/redo05.log';
Database altered.
SQL> alter database rename file '/data/zmmis/mdm/mdm/redo06.log' to '/bak/dbf/mdm/mdm/redo06.log';
Database altered.
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
问题一：ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[34674], [265317], [267084], [], [], [], [], [], [], []
启动报错，百度了一下说是数据库数据异常断电，导致LGWR写联机日志文件时失败，下次重新启动数据库时，需要做实例级恢复，而又无法从联机日志文件里获取到这些redo信息，因为上次断电时，写日志失败了。
SQL> startup;
ORACLE instance started.
Total System Global Area 1.0957E+10 bytes
Fixed Size 2216504 bytes
Variable Size 5838474696 bytes
Database Buffers 5100273664 bytes
Redo Buffers 15745024 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],
[], [], [], [], [], [], [], []
--查看当前重作日志文件情况，从以下查询结果可以看到当前日志组为最新的为22879，redo01
SQL> select group#,sequence#,status,first_time,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_TIME NEXT_CHANGE#
---------- ---------- ---------------- ------------ ------------
1 22879 CURRENT 17-FEB-19 2.8147E+14
2 22874 INACTIVE 10-FEB-19 1.0805E+13
6 22877 INACTIVE 14-FEB-19 1.0805E+13
4 22878 INACTIVE 16-FEB-19 1.0805E+13
5 22876 INACTIVE 13-FEB-19 1.0805E+13
3 22875 INACTIVE 11-FEB-19 1.0805E+13
6 rows selected.
--恢复数据库，指定redo01.log日志，红色为redo日志位置
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10805487491086 generated at 02/17/2019 11:50:36 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/MDM/archivelog/2019_02_17/o1_mf_1_22879_%u_.
arc
ORA-00280: change 10805487491086 for thread 1 is in sequence #22879
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/bak/dbf/mdm/mdm/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
重启数据库，正常
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1.0957E+10 bytes
Fixed Size 2216504 bytes
Variable Size 6174019016 bytes
Database Buffers 4764729344 bytes
Redo Buffers 15745024 bytes
Database mounted.
Database opened.
用PS/SQL连接测试，是否正常
问题一：启动报错，百度了一下说是数据库数据异常断电，导致LGWR写联机日志文件时失败，下次重新启动数据库时，需要做实例级恢复，而又无法从联机日志文件里获取到这些redo信息，因为上次断电时，写日志失败了。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
