实验目的:Oracle直接拷贝物理文件迁移,生产库有类似施工需求,故在实验环境简单验证一下。
实验环境:
A主机:192.168.1.200 Solaris10 Oracle 11.2.0.1
B主机:192.168.1.186 Solaris10
1.备份ORACLE安装包($ORACLE_BASE目录)
代码语言:javascript复制tar cvf oracle.tar oracle
2.关库,备份参数文件,控制文件,数据文件,临时文件,日志文件
查看各个文件的存放路径:
代码语言:javascript复制SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfileprod.ora
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/prod/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/prod/contr
ol02.ctl
control_management_pack_access string DIAGNOSTIC TUNING
SQL> set linesize 160
SQL> col member for a70
SQL> 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 NO
SQL> 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.dbf
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/temp01.dbf
SQL>
此实验环境的参数文件,控制文件,数据文件,联机重做日志文件,均在$ORACLE_BASE目录下,不用单独备份了,实际生产情况下,一般肯定是不在$ORACLE_BASE的路径下,此时需要分别记录好路径并备份这些文件。
3.备份的安装包和所有文件传输到目标机器186
代码语言:javascript复制scp oracle.tar 192.168.1.186:/u01/app/
tar xvf oracle.tar
4.等待传输和解压的过程中可以先对新主机做一些配置
4.1新建oracle用户和组
代码语言:javascript复制groupadd oinstall
groupadd dba
useradd -g oinstall -G dba -d /export/home/oracle -m -s /usr/bin/bash -c "oracle user" oracle
4.2配置环境变量 vi ~/.profile
代码语言:javascript复制ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
ORACLE_SID=prod
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
使环境变量生效
代码语言:javascript复制source ~/.profile
4.3root用户修改/etc/system,添加:
代码语言:javascript复制set shmsys:shminfo_shmmax=4294967295 //共享内存字节数(一般设置为物理内存的一半)
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=200
set shmsys:shminfo_shmseg=200
set semsys:seminfo_semmap=1024
set semsys:seminfo_semmns=2048
set semsys:seminfo_semmni=2048
set semsys:seminfo_semmsl=2048
set semsys:seminfo_semmnu=2048
set semsys:seminfo_semume=200
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767
重启主机生效配置
代码语言:javascript复制sync;sync;sync;reboot
5.至此,所有文件均已经拷贝到新主机
5.1尝试启动数据库
代码语言:javascript复制[oracle@solaris186:/export/home/oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 15 10:17:24 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2211488 bytes
Variable Size 234881376 bytes
Database Buffers 163577856 bytes
Redo Buffers 4349952 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],
[], [], [], [], [], [], [], []
SQL> select status from v$instance;
STATUS
------------
MOUNTED
5.2尝试恢复数据库
代码语言:javascript复制SQL> recover database;
Media recovery complete.
跟踪下recover过程的告警日志
代码语言:javascript复制Sun Jun 15 10:33:00 2014
db_recovery_file_dest_size of 3882 MB is 1.89% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sun Jun 15 10:50:15 2014
ALTER DATABASE RECOVER database
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/prod/redo01.log
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER database
5.3尝试正常打开数据库
代码语言:javascript复制SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL>
跟踪下open库的告警日志,没有问题。
代码语言:javascript复制Sun Jun 15 10:50:28 2014
alter database open
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 596 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 4, block 3095
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/prod/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 4, block 4288, scn 1302225
0 data blocks read, 0 data blocks written, 596 redo k-bytes read
Sun Jun 15 10:50:29 2014
LGWR: STARTING ARCH PROCESSES
Sun Jun 15 10:50:29 2014
ARC0 started with pid=20, OS id=1944
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Sun Jun 15 10:50:30 2014
ARC1 started with pid=21, OS id=1946
Sun Jun 15 10:50:30 2014
ARC2 started with pid=22, OS id=1948
ARC1: Archival started
ARC2: Archival started
Sun Jun 15 10:50:30 2014
ARC3 started with pid=23, OS id=1950
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 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.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Jun 15 10:50:30 2014
SMON: enabling cache recovery
Archived Log entry 23 added for thread 1 sequence 4 ID 0xf4f6282 dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
Sun Jun 15 10:50:39 2014
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sun Jun 15 10:50:42 2014
QMNC started with pid=24, OS id=1968
Sun Jun 15 10:50:51 2014
Completed: alter database open
Sun Jun 15 10:50:59 2014
Starting background process CJQ0
Sun Jun 15 10:50:59 2014
CJQ0 started with pid=30, OS id=1988
Setting Resource Manager plan SCHEDULER[0x3009]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun Jun 15 10:51:04 2014
Starting background process VKRM
Sun Jun 15 10:51:05 2014
VKRM started with pid=28, OS id=1991
Sun Jun 15 10:51:22 2014
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Sun Jun 15 10:51:47 2014
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Sun Jun 15 10:55:47 2014
Starting background process SMCO
Sun Jun 15 10:55:49 2014
SMCO started with pid=32, OS id=2027
实际生产环境中的施工,并没有遇到啥问题,很顺利搞定了此次需求 。