说明:
数据库迁移几大因素:
- 停机时间
- 源端和目标端:操作系统平台、版本、对应的数据库版本
- 数据量多少
- 外界因素:存储空间、网络等
RAC为生产环境,基于RMAN备份恢复将生产环境恢复到单机上;单机环境只安装oralce数据库软件,不创建实例。
一、对于RAC环境
1、RAC环境信息
查看DBID:
select dbid from v$database;
查看SCN:
select FIRST_CHANGE#,NEXT_CHANGE#,STATUS from v$log;
查看数据文件、日志文件、临时文件
select group#,member from v$logfile;
select file#,name from v$datafile;
select name from v$tempfile;
创建pfile:
create pfile from spfile;
2、rman备份
注:先备份数据库和归档日志,再备份控制文件,因为rman备份信息存储在控制文件中。
rman备份一个全备FULL BACKUPSET:
rman target /
run{
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
backup database format '/u01/oracle/backup/db_%U_%T.bak';
sql 'alter system archive log current';
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup archivelog format '/u01/oracle/backup/arch_%U_%T.bak';
backup spfile format '/u01/oracle/backup/spfile_%U_%T.bak';
backup current controlfile format '/u01/oracle/backup/ctl_%U_%T.bak';
release channel c1 ;
release channel c2 ;
delete noprompt obsolete;
crosscheck backup;
delete noprompt expired backup;
}
将pfile 与备份文传到单机上、(归档文件备份无效、 可选择将归档原文件传到单机上或弃用)
二、对于单机环境
1、修改单实例的参数文件
将备份的文件拷贝到单实例的相同目录
手动创建pfile文件,或者从备份的pfile文件进行修改:
db_name=orcl
log_archive_dest_1='location=/arch'
pga_aggregate_target=4G
sga_target=8G
compatible='11.2.0.4.0'
control_files='/u01/app/oracle/oradata/control.ctl'
参数文件参考如下:
vi initorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.dbf'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/'
*.db_create_online_log_dest_1='/u01/app/oracle/oradata/'
*.db_domain=''
*.db_files=2000
*.db_name='orcl'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 TRACE NAME CONTEXT FOREVER, LEVEL 1'
*.log_archive_dest_1='LOCATION=/arch'
*.log_archive_format='%t_%s_%r.dbf'
*.max_dump_file_size='1G'
*.open_cursors=300
*.pga_aggregate_target=288358400
*.processes=1500
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.sessions=1655
*.sga_max_size=1100m
*.sga_target=1100m
2、创建单机环境使用的目录
--FRA目录
mkdir -p /u01/app/oracle/fast_recovery_area/orcl
--DATAFILE
mkdir -p /u01/app/oracle/oradata/orcl
--adump
mkdir -p /u01/app/oracle/admin/orcl/adump
--配置权限
chown -R oracle.oinstall /u01/app/oracle
3、创建密码文件,注意密码文件的位置(mos文档没有)
[oracle@orc1 dbs]$ orapwd file=orapworcl password=oracle entries=30
4、通过编辑好的初始化参数重启数据库到NOMOUNT
启动单实例到nomount:
[oracle@11g1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 12 20:39:56 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/backup/initorcl.ora';
ORACLE instance started.
Total System Global Area 7975003968 bytes
Fixed Size 2257472 bytes
Variable Size 222301632 bytes
Database Buffers 566231040 bytes
Redo Buffers 6733824 bytes
用pfile生成spfile文件:
SQL> create spfile from pfile;
SQL> shutdown immediate;
SQL> startup nomount;
5、从备份集恢复控制文件,并启动到mount状态
[oracle@orcl ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 12 20:40:14 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: orcl (not mounted)
RMAN> set DBID=3882088725;
RMAN> restore controlfile from '/u01/oracle/backup/clt_0dpq0ka5_1_1_20141212.bak'; --建议使用
Starting restore at 12-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/control.ctl
Finished restore at 12-DEC-14
RMAN> alter database mount;
6、恢复数据文件
6.1 将备份注册进控制文件里:
RMAN> catalog start with '/u01/oracle/backup/';
RMAN> list backup;
RMAN> list backup of archivelog all;
确认数据文件、联机日志文件、临时文件路径,通过RMAN重命名数据文件和临时文件,进行还原。
6.2 修改数据文件路径(可以进行批量修改),恢复数据文件:
SQL> set pages 999
SQL> set line 500
SQL> select 'alter database rename file ''' || name || ''' to ' || '''/u01/app/oracle/oradata/orcl/' ||
substr(name,instr(name, '/', -1)+1,instr(name, '.', -1)-instr(name, '/', -1)-1) || '.dbf''' || ';'
from v$datafile;
RMAN>
run{
set newname for datafile 1 to '/u01/app/oracle/data/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/data/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/data/UNDOTBS1.dbf';
set newname for datafile 4 to '/u01/app/oracle/data/users01.dbf';
set newname for datafile 5 to '/u01/app/oracle/data/UNDOTBS2.dbf';
set newname for datafile 6 to '/u01/app/oracle/data/siebeldata.268.dbf';
set newname for datafile 7 to '/u01/app/oracle/data/siebelindex.dbf';
set newname for datafile 8 to '/u01/app/oracle/data/prod_mds.dbf';
set newname for datafile 9 to '/u01/app/oracle/data/prod_biplatform.271.dbf';
set newname for datafile 10 to '/u01/app/oracle/data/prd_biplatform.273.dbf';
set newname for datafile 11 to '/u01/app/oracle/data/prd_mds.dbf';
set newname for datafile 12 to '/u01/app/oracle/data/siebeldata.dbf';
restore database;
switch datafile all;
}
6.3 恢复数据库:
RMAN> recover database;
# 恢复完成后,将库启动到read only模式,查询一下数据是否正常
SQL> alter database open read only;
6.4 修改联机日志文件的路径:
# 查看日志文件:
SQL> select * from v$logfile;
SQL> select member from v$logfile;
SQL> select 'alter database rename file ''' || member ||'''' || ' to '''|| substr(member,0,instr(member,'/',-1) -1)|| '_ST' || substr(member,instr(member,'/',-1)) ||''';'
from v$logfile;
# 修改联机日志文件的路径:
alter database rename file '+ASMDATA/orcl/onlinelog/group_1.267.781033527' to '/u01/app/oracle/oradata/orcl/redo1_1.log';
alter database rename file '+RECOVERY/orcl/onlinelog/group_1.257.781033533' to '/u01/app/oracle/oradata/orcl/redo1_2.log';
alter database rename file '+ASMDATA/orcl/onlinelog/group_2.266.781033537' to '/u01/app/oracle/oradata/orcl/redo2_1.log';
alter database rename file '+RECOVERY/orcl/onlinelog/group_2.258.781033543' to '/u01/app/oracle/oradata/orcl/redo2_2.log';
alter database rename file '+ASMDATA/orcl/onlinelog/group_3.259.781033803' to '/u01/app/oracle/oradata/orcl/redo3_1.log';
alter database rename file '+RECOVERY/orcl/onlinelog/group_3.260.781033809' to '/u01/app/oracle/oradata/orcl/redo3_2.log';
alter database rename file '+ASMDATA/orcl/onlinelog/group_4.258.781033815' to '/u01/app/oracle/oradata/orcl/redo4_1.log';
alter database rename file '+RECOVERY/orcl/onlinelog/group_4.261.781033821' to '/u01/app/oracle/oradata/orcl/redo4_2.log';
alter database rename file '+ASMDATA/orcl/onlinelog/group_5.262.781033549' to '/u01/app/oracle/oradata/orcl/redo5_1.log';
alter database rename file '+RECOVERY/orcl/onlinelog/group_5.259.781033555' to '/u01/app/oracle/oradata/orcl/redo5_2.log';
6.5 恢复归档日志文件:
注:后续还有日志文件需要恢复,千万别RESETLOGS打开数据库,等迁移恢复完日志文件后再resetlogs打开库,否则数据会不一致。
# 将归档日志拷贝到目标机(scp归档)
cd /u01/app/oracle/archivelog/
ll -lrt
scp 1_6948_917786690.dbf 172.16.36.30:/u01/app/oracle/archivelog/
# 把归档日志注册到rman
RMAN> catalog archivelog '/u01/app/oracle/archivelog/arch_1_72_929977336.arc','/u01/app/oracle/archivelog/arch_1_73_929977336.arc';
或者:
RMAN> catalog start with '/u01/app/oracle/archivelog/';
RMAN> recover database;
6.6 RESETLOGS打开数据库:
SQL> alter database open resetlogs;
三、后续操作
1、查看redo log 信息,并删除无效日志组:
查看redo log 信息,并删除无效日志组(节点2日志)
select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PUBLIC
select group# from v$log where THREAD#=2;
GROUP#
----------
3
4
6
8
SQL> alter database disable thread 2;
SQL> alter database drop logfile group 3;
SQL> alter database drop logfile group 4;
SQL> alter database drop logfile group 6;
SQL> alter database drop logfile group 8;
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
创建新的redo文件:
alter database add logfile group 5 '/u01/app/oracle/data/redo1.log' size 500M;
alter database add logfile group 6 '/u01/app/oracle/data/redo2.log' size 500M;
alter database add logfile group 7 '/u01/app/oracle/data/redo3.log' size 500M;
2、查看undo表空间,并删除节点2(在此不使用)的undo表空间
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
3、查看temp表空间,并重建temp表空间
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_cr602c5z_.tmp
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP
SQL> create temporary tablespace TEMP1 tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 50M;
SQL> alter database default temporary tablespace TEMP1;
SQL> drop tablespace TEMP including contents and datafiles;
4、查看监听状态并配置,编辑tnsname.ora文件。(mos文档没有)
[oracle@orcl ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@rac1 admin]$ vi tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.154)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
5、通过以下命令创建密码文件,注意密码文件的位置(mos文档没有)
[oracle@orc1 dbs]$ orapwd file=orapworcl password=oracle entries=30
6、将新数据库添加到oratab中
[oracle@orcl dbs]$ vi /etc/oratab
orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N