首页 > 代码库 > 实验：Oracle直接拷贝物理存储文件迁移

实验：Oracle直接拷贝物理存储文件迁移

2024-07-08 07:09:39   226人阅读

实验目的：Oracle直接拷贝物理文件迁移，生产库有类似施工需求，故在实验环境简单验证一下。
实验环境：
A主机：192.168.1.200 Solaris10 + Oracle 11.2.0.1
B主机：192.168.1.186 Solaris10
 
1.备份ORACLE安装包($ORACLE_BASE目录)
tar cvf oracle.tar oracle
 
2.关库，备份参数文件，控制文件，数据文件，临时文件，日志文件
查看各个文件的存放路径：
SQL> show parameter pfileNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------spfile                               string      /u01/app/oracle/product/11.2.0                                                 /db_1/dbs/spfileprod.oraSQL> show parameter control  NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------control_file_record_keep_time        integer     7control_files                        string      /u01/app/oracle/oradata/prod/c                                                 ontrol01.ctl, /u01/app/oracle/                                                 flash_recovery_area/prod/contr                                                 ol02.ctlcontrol_management_pack_access       string      DIAGNOSTIC+TUNINGSQL> set linesize 160SQL> col member for a70SQL> select  * from v$logfile;    GROUP# STATUS  TYPE    MEMBER                                                                 IS_---------- ------- ------- ---------------------------------------------------------------------- ---         3         ONLINE  /u01/app/oracle/oradata/prod/redo03.log                                NO         2         ONLINE  /u01/app/oracle/oradata/prod/redo02.log                                NO         1         ONLINE  /u01/app/oracle/oradata/prod/redo01.log                                NOSQL> select name from v$datafile;NAME------------------------------------------------------------------------------------------------------------------------/u01/app/oracle/oradata/prod/system01.dbf/u01/app/oracle/oradata/prod/sysaux01.dbf/u01/app/oracle/oradata/prod/undotbs01.dbf/u01/app/oracle/oradata/prod/users01.dbf/u01/app/oracle/oradata/prod/dbs_d_wrnophq.dbfSQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/prod/temp01.dbfSQL>

此实验环境的参数文件，控制文件，数据文件，联机重做日志文件,均在$ORACLE_BASE目录下，不用单独备份了，实际生产情况下，一般肯定是不在$ORACLE_BASE的路径下，此时需要分别记录好路径并备份这些文件。

 
3.备份的安装包和所有文件传输到目标机器186
scp oracle.tar 192.168.1.186:/u01/app/tar xvf oracle.tar
4.等待传输和解压的过程中可以先对新主机做一些配置
4.1新建oracle用户和组
groupadd oinstallgroupadd dbauseradd -g oinstall -G dba -d /export/home/oracle -m -s /usr/bin/bash -c "oracle user" oracle

4.2配置环境变量 vi ~/.profile

ORACLE_BASE=/u01/app/oracleORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1ORACLE_SID=prodPATH=$PATH:$ORACLE_HOME/binexport ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
使环境变量生效
source ~/.profile
4.3root用户修改/etc/system，添加：
set shmsys:shminfo_shmmax=4294967295     //共享内存字节数(一般设置为物理内存的一半)set shmsys:shminfo_shmmin=1set shmsys:shminfo_shmmni=200set shmsys:shminfo_shmseg=200set semsys:seminfo_semmap=1024set semsys:seminfo_semmns=2048set semsys:seminfo_semmni=2048set semsys:seminfo_semmsl=2048set semsys:seminfo_semmnu=2048set semsys:seminfo_semume=200set semsys:seminfo_semopm=100set semsys:seminfo_semvmx=32767

重启主机生效配置

sync;sync;sync;reboot
5.至此，所有文件均已经拷贝到新主机
5.1尝试启动数据库
[oracle@solaris186:/export/home/oracle]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 15 10:17:24 2014Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  405020672 bytesFixed Size                  2211488 bytesVariable Size             234881376 bytesDatabase Buffers          163577856 bytesRedo Buffers                4349952 bytesDatabase mounted.ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],[], [], [], [], [], [], [], []SQL> select status from v$instance;STATUS------------MOUNTED
5.2尝试恢复数据库
SQL> recover database;Media recovery complete.
跟踪下recover过程的告警日志
Sun Jun 15 10:33:00 2014db_recovery_file_dest_size of 3882 MB is 1.89% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.Sun Jun 15 10:50:15 2014ALTER DATABASE RECOVER  database  Media Recovery StartSerial Media Recovery startedRecovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0  Mem# 0: /u01/app/oracle/oradata/prod/redo01.logMedia Recovery Complete (prod)Completed: ALTER DATABASE RECOVER  database
5.3尝试正常打开数据库
SQL> alter database open;Database altered.SQL> select status from v$instance;STATUS------------OPENSQL>
跟踪下open库的告警日志，没有问题。
Sun Jun 15 10:50:28 2014alter database openBeginning crash recovery of 1 threadsStarted redo scanCompleted redo scanread 596 KB redo, 0 data blocks need recoveryStarted redo application atThread 1: logseq 4, block 3095Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0  Mem# 0: /u01/app/oracle/oradata/prod/redo01.logCompleted redo application of 0.00MBCompleted crash recovery atThread 1: logseq 4, block 4288, scn 13022250 data blocks read, 0 data blocks written, 596 redo k-bytes readSun Jun 15 10:50:29 2014LGWR: STARTING ARCH PROCESSESSun Jun 15 10:50:29 2014ARC0 started with pid=20, OS id=1944 ARC0: Archival startedLGWR: STARTING ARCH PROCESSES COMPLETEARC0: STARTING ARCH PROCESSESSun Jun 15 10:50:30 2014ARC1 started with pid=21, OS id=1946 Sun Jun 15 10:50:30 2014ARC2 started with pid=22, OS id=1948 ARC1: Archival startedARC2: Archival startedSun Jun 15 10:50:30 2014ARC3 started with pid=23, OS id=1950 ARC1: Becoming the ‘no FAL‘ ARCHARC1: Becoming the ‘no SRL‘ ARCHARC2: Becoming the heartbeat ARCHThread 1 advanced to log sequence 5 (thread open)Thread 1 opened at log sequence 5  Current log# 2 seq# 5 mem# 0: /u01/app/oracle/oradata/prod/redo02.logSuccessful open of redo thread 1MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setSun Jun 15 10:50:30 2014SMON: enabling cache recoveryArchived Log entry 23 added for thread 1 sequence 4 ID 0xf4f6282 dest 1:ARC3: Archival startedARC0: STARTING ARCH PROCESSES COMPLETESuccessfully onlined Undo Tablespace 2.Verifying file header compatibility for 11g tablespace encryption..Verifying 11g file header compatibility for tablespace encryption completedSMON: enabling tx recoveryDatabase Characterset is WE8MSWIN1252No Resource Manager plan activeSun Jun 15 10:50:39 2014replication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCSun Jun 15 10:50:42 2014QMNC started with pid=24, OS id=1968 Sun Jun 15 10:50:51 2014Completed: alter database openSun Jun 15 10:50:59 2014Starting background process CJQ0Sun Jun 15 10:50:59 2014CJQ0 started with pid=30, OS id=1988 Setting Resource Manager plan SCHEDULER[0x3009]:DEFAULT_MAINTENANCE_PLAN via scheduler windowSetting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameterSun Jun 15 10:51:04 2014Starting background process VKRMSun Jun 15 10:51:05 2014VKRM started with pid=28, OS id=1991 Sun Jun 15 10:51:22 2014Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"Sun Jun 15 10:51:47 2014End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"Sun Jun 15 10:55:47 2014Starting background process SMCOSun Jun 15 10:55:49 2014SMCO started with pid=32, OS id=2027
6.实际生产环境中的施工，并没有遇到啥问题，很顺利搞定了此次需求 。


联系
我们