实验一:文件系统迁移到ASM
1、首先打开归档,确认归档路径
2、查询下数据库文件结构
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/PROD4/PROD4/system01.dbf /u01/app/oracle/oradata/PROD4/PROD4/sysaux01.dbf /u01/app/oracle/oradata/PROD4/PROD4/undotbs01.dbf /u01/app/oracle/oradata/PROD4/PROD4/users01.dbf /u01/app/oracle/oradata/PROD4/PROD4/example01.dbf SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/PROD4/PROD4/control01.ctl /u01/app/oracle/oradata/PROD4/PROD4/control02.ctl /u01/app/oracle/oradata/PROD4/PROD4/control03.ctl SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/PROD4/PROD4/redo03.log /u01/app/oracle/oradata/PROD4/PROD4/redo02.log /u01/app/oracle/oradata/PROD4/PROD4/redo01.log SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/PROD4/PROD4/temp01.dbf
3、全库做备份:open状态就可以
[oracle@edsir1p8-PROD4 ~]$ rman target / RMAN> BACKUP as copy database format '+DATA'; RMAN> EXIT
4、 查看asm磁盘组中备份的控制文件的路径
RMAN> list backup of controlfile; specification does not match any backup in the repository RMAN> list copy of controlfile; List of Control File Copies =========================== Key S Completion Time Ckp SCN Ckp Time ------- - --------------- ---------- --------------- 5 A 17-MAY-16 871730 17-MAY-16 Name: +DATA/prod4/controlfile/backup.260.912089587 Tag: TAG20160517T141203
5、修改spfile文件中控制文件的路径信息
[oracle@edsir1p8-PROD4 ~]$ sqlplus / as sysdba SQL> alter system set control_files='+DATA/prod4/controlfile/backup.260.91208958' scope=spfile; SQL>shutdown immediate SQL> startup mount; rman target / RMAN> catalog start with '+data'; RMAN> switch database to copy; RMAN> recover database; RMAN> alter database open resetlogs;
指定resetlogs,会执行下列操作:
1)将当前的在线重做日志文件归档,然后清空其内容并将日志文件序列号重置为1
2)重置控制文件中关于在线重做日志文件的元数据
3)更新数据文件和在线重做日志文件的resetlogs scn等信息
6、创建新的临时表空间文件,并删除旧的
SQL> alter tablespace temp add tempfile '+DATA' size 200M; SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/PROD4/PROD4/temp01.dbf';
7、把redo添加新的成员,删除老的成员,查看原来有多少组,就添加多少成员
select member from v$logfile; alter database add logfile member '+data' to group 1; alter database add logfile member '+data' to group 2; alter database add logfile member '+data' to group 3; select group#,status,member from v$logfile;
初始化新加的logfile
SQL> alter system switch logfile;--最少执行三遍 select group#,status,member from v$logfile;--查询下日志成员状态
删除旧的logfile member(如果无法删除,说明该文件还在使用,我们可以进行手工切换之后,再删除)
SQL> select group#,members,status from v$log; GROUP# MEMBERS STATUS ---------- ---------- ---------------- 1 2 CURRENT 2 2 INACTIVE 3 2 INACTIVE SQL> alter system switch logfile; alter database drop logfile member '+DATA/prod4/onlinelog/group_3.266.930712143'; alter database drop logfile member '+DATA/prod4/onlinelog/group_2.265.930712141'; alter database drop logfile member '+DATA/prod4/onlinelog/group_1.264.930712139'; alter database drop logfile member '/u01/app/oracle/oradata/PROD4/PROD4/redo03.log'; alter database drop logfile member '/u01/app/oracle/oradata/PROD4/PROD4/redo02.log'; alter database drop logfile member '/u01/app/oracle/oradata/PROD4/PROD4/redo01.log';
实验二:旧存储创建DATA磁盘组—–>新存储创建的DATA2磁盘组
因为主机不换,只换存储,为了减少停机时间,我们需要在同一台主机上重新创建一个新的实例来迁移数据
1、以copy的方式全库做备份:open状态就可以
[oracle@edsir1p8-PROD4 ~]$ rman target / RMAN> BACKUP as copy database format '+DATA2'; RMAN> EXIT
2、创建一个pfile文件
[oracle@edsir1p8-PROD4 ~]$ sqlplus / as sysdba SQL>create pfile from spfile; cd $ORACLE_HOME/dbs [oracle@edsir1p8-P4 dbs]$ mv initPROD4.ora initP4.ora [oracle@edsir1p8-PROD4 ~]$export ORACLE_SID=P4 [oracle@edsir1p8-PROD4 ~]$ sqlplus / as sysdba SQL>STARTUP NOMOUNT; SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ P4 STARTED
3、 查看asm磁盘组中备份的控制文件的路径
RMAN> list backup of controlfile; specification does not match any backup in the repository RMAN> list copy of controlfile; List of Control File Copies =========================== Key S Completion Time Ckp SCN Ckp Time ------- - --------------- ---------- --------------- 5 A 17-MAY-16 871730 17-MAY-16 Name: +DATA2/prod4/controlfile/backup.260.912089587 Tag: TAG20160517T141203
4、修改spfile文件中控制文件的路径信息
[oracle@edsir1p8-PROD4 ~]$ sqlplus / as sysdba SQL> alter system set control_files='+DATA2/prod4/controlfile/backup.260.91208958' scope=spfile; SQL>STARTUP FORCE MOUNT; --报错,因为非rac环境,dbs目录下的lkPROD4 MOUNT锁文件会阻止两个实例同时 mount 在同一数据库上 我们修改下dbs下的锁文件然后再次mount P4数据库实例: [oracle@edsir1p8-P4 dbs]$ mv lkPROD4 lkPROD4.BAK sql>alter database mount;
5、使用rman工具进行转换迁移
rman target / RMAN> switch database to copy; RMAN> recover database; ---实际生产上我们可能要多次recover database;
验证下数据文件的路径是否指向新的磁盘组:
sql>select name from v$datafile
6、为验证生产库PROD4不停机的过程中,产生的增量数据不丢失,我们创建一个测试表:
sql>create table test as select * from dba_objects; sql>insert into test select * from test; sql>commit; sql>alter system archive log current;--可多执行几遍,然后查下当行日志号 sql>archive log list;
7、rman登录到P4实例再执行recover database;
rman> recover database; --观察是否已经把刚生产的归档文件追完,如果是,那就赶紧把生产上的应用停下来。
8、停止应用后,生产数据库PROD4的数据不再发生变化,我们切几次日志,将当前redo中最新的数据生产归档,
再去P4实例做recover database;
9、在P4实例上以read only模式打开,较验P4数据库实例和PROD4数据库实例数据是否一至
sql>alter database open read only; sql>select count(*) from test; sql>select count(*) from test@PROD4;
10、验证迁移后的数据和迁移前数据是一致的,重启P4数据库实例,以open resetlogs 模式打开,原来的PROD4实例可以关闭
sql>startup force mount;
11、在mount状态修改日志文件位置后再OPEN:
alter database rename file '+DATA/prod4/onlinelog/group_1.256.913788033' to '+data2/prod4/onlinelog/group_1.256.913788033'; alter database rename file '+DATA/prod4/onlinelog/group_2.256.913788033' to '+data2/prod4/onlinelog/group_2.256.913788033'; alter database rename file '+DATA/prod4/onlinelog/group_3.256.913788033' to '+data2/prod4/onlinelog/group_3.256.913788033'; alter database open restlogs;
8、修改临时表空间数据文件的位置:
select name from v$tempfile; alter tablespace temp add tempfile '+data2' size 100m; alter tablespace temp drop tempfile '+data/prod4/tempfile/TEMP.263.930712043';
11、把P4实例的spfile文件名字改回原来数据库PROD4的spfile文件名。这样再正常启动PROD4库自动会读取迁移后控制文件 和数据文件以及日志文件。#########################################################
###实验三:
使用备份集迁移到不同磁盘组(DATA2—->data3):应用场景:把生产环境的备份集恢复到测试环境,但是测试环境的asm磁盘组的名称不同
1、在生产库上做一份全备:(所有数据文件,控制文件、归档文件)
2、把生产库的spfile文件传输到测试库
cp spfilePR0D4.ora spfileTEST.ora
2.登录数据库
export ORACLE_SID=TEST sqlplus / as sysdba sql>startup nomount;
4、恢复控制文件
rman target / rman>restore controlfile to '+data3' from '/home/oracle/bak/prod4_ctl.bak';
5、修改参数文件
先打开asm实例,然后通过asmcmd控制台找到上一步生成的控制文件的路径。
SQL>ALTER SYSTEM SET CONTROL_FILES='+DATA3/prod4/datafile/controlfile/control01_234234324234' scope=spfile; rm -rf lkPROD4 SQl>STARTUP FORCE MOUNT;
6、恢复数据文件到不同磁盘组下:
run{ set newname for datafile '/u01/app/oracle/oradata/PROD4/PROD4/system01.dbf' to '+data'; set newname for datafile '/u01/app/oracle/oradata/PROD4/PROD4/sysaux01.dbf' to '+data'; set newname for datafile '/u01/app/oracle/oradata/PROD4/PROD4/undotbs01.dbf' to '+data'; set newname for datafile '/u01/app/oracle/oradata/PROD4/PROD4/users01.dbf' to '+data'; set newname for datafile '/u01/app/oracle/oradata/PROD4/PROD4/example01.dbf' to '+data'; restore database; switch datafile all; recover database; } /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_bpy12gck_.dbf /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_bpy12gfm_.dbf /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_bpy12ggz_.dbf /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_bpy12gjt_.dbf /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_bpy15nf0_.dbf
7、在mount状态修改日志文件位置后再OPEN:
alter database rename file '/u01/app/oracle/oradata/PROD4/PROD4/redo01.log' to '+data/prod4/onlinelog/group_1.256.913788033'; alter database rename file '/u01/app/oracle/oradata/PROD4/PROD4/redo02.log' to '+data/prod4/onlinelog/group_2.256.913788033'; alter database rename file '/u01/app/oracle/oradata/PROD4/PROD4/redo03.log' to '+data/prod4/onlinelog/group_3.256.913788033'; alter database open restlogs;
8、修改临时表空间数据文件的位置:
select name from v$tempfile; alter tablespace temp add tempfile '+data3' size 100m; atler tablesapce temp drop tempfile '+DATA2/datafile/tempfile234234323';