使用rman copy将数据库迁移到ASM实例

最新推荐文章于 2021-04-05 22:48:52 发布

cqis22460

最新推荐文章于 2021-04-05 22:48:52 发布

阅读量235

点赞数

文章标签：数据库操作系统网络

第一部分 . 概述

一、实验环境

Os：

[oracle@lzc dblzc]$ lsb_release

LSB Version: :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch

[oracle@lzc dblzc]$ lsb_release -a

LSB Version: :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch

Distributor ID: RedHatEnterpriseServer

Description: Red Hat Enterprise Linux Server release 5.4 (Tikanga)

Release: 5.4

Codename: Tikanga

ASM DISKGROUP：

[oracle@lzc dbs]$ asmcmd

ASMCMD> ls

DGROUPA/

ASMCMD>

Database：

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

二、实验目标

将一个文件系统数据库testdb移植到asm实例上

三、实验设计

1、查看数据库中控制文件，在线日志，临时文件名

2、修改参数文件

3、执行rman copy

4、重命名在线日志

4、后期处理

第二部分 . 技术概述

一、实验步骤

1. 查看数据库中控制文件，在线日志，临时文件名

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/testdb/redo03.log

/u01/app/oracle/oradata/testdb/redo02.log

/u01/app/oracle/oradata/testdb/redo01.log

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/testdb/control01.ctl

/u01/app/oracle/oradata/testdb/control02.ctl

/u01/app/oracle/oradata/testdb/control03.ctl

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/testdb/temp01.dbf

2. 修改参数文件

修改参数文件的重点在修改db_create_online_log_dest_1、db_create_file_dest这两个参数如果你开启了闪回恢复区，最好就把db_recovery_file_dest参数也设置到ASM磁盘上。最后去掉参数文件中的control_files这个参数。这里由于我使用的是spfile所以我就在线修改之后再，转储为pfile。

a) 在线修改参数

SQL> ALTER SYSTEM SET db_create_file_dest='+DGROUPA';

System altered.

SQL> ALTER SYSTEM SET db_create_online_log_dest_1='+DGROUPA';

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest='+DGROUPA';

System altered.

b) 从spfile创建pfile

SQL> CREATE PFILE='/home/oracle/exercise/pfile_testdb_asm.ora' from spfile;

File created.

c) 备份控制文件

SQL> alter database backup controlfile to '/home/oracle/exercise/testdb_asm.ctl';

Database altered.

d) 关闭数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

e) 修改生成pfile 文件

将control_files这个参数去掉

*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'

*.background_dump_dest='/u01/app/oracle/admin/testdb/bdump'

*.compatible='10.2.0.1.0'

*.core_dump_dest='/u01/app/oracle/admin/testdb/cdump'

*.db_block_size=8192

*.db_create_file_dest='+DGROUPA'

*.db_create_online_log_dest_1='+DGROUPA'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='testdb'

*.db_recovery_file_dest_size=2147483648

*.db_recovery_file_dest='+DGROUPA'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'

*.job_queue_processes=10

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

3. 执行rman copy

Run{

STARTUP NOMOUNT PFILE='/home/oracle/exercise/pfile_testdb_asm.ora';

RESTORE CONTROLFILE FROM '/home/oracle/exercise/testdb_asm.ctl';//恢复备份的控制文件

ALTER DATABASE MOUNT;

BACKUP AS COPY DATABASE FORMAT '+dgroupa';//这里的备份仅限于数据文件

RECOVER DATABASE;//只要前面使用了restore就需要recover进行同步

}

SWITCH DATABASE TO COPY;

Oracle instance started

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 92276304 bytes

Database Buffers 188743680 bytes

Redo Buffers 2973696 bytes

Starting restore at 12-SEP-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: copied control file copy

output filename=+DGROUPA/testdb/controlfile/current.265.793833703

Finished restore at 12-SEP-12

database mounted

released channel: ORA_DISK_1

Starting backup at 12-SEP-12

Starting implicit crosscheck backup at 12-SEP-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

Finished implicit crosscheck backup at 12-SEP-12

Starting implicit crosscheck copy at 12-SEP-12

using channel ORA_DISK_1

Finished implicit crosscheck copy at 12-SEP-12

searching for all files in the recovery area

cataloging files...

no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/u01/app/oracle/oradata/testdb/system01.dbf

output filename=+DGROUPA/testdb/datafile/system.266.793833717 tag=TAG20120912T212155 recid=1 stamp=793833808

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:38

channel ORA_DISK_1: starting datafile copy

input datafile fno=00003 name=/u01/app/oracle/oradata/testdb/sysaux01.dbf

output filename=+DGROUPA/testdb/datafile/sysaux.267.793833815 tag=TAG20120912T212155 recid=2 stamp=793833870

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06

channel ORA_DISK_1: starting datafile copy

input datafile fno=00002 name=/u01/app/oracle/oradata/testdb/undotbs01.dbf

output filename=+DGROUPA/testdb/datafile/undotbs1.268.793833879 tag=TAG20120912T212155 recid=3 stamp=793833884

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile fno=00004 name=/u01/app/oracle/oradata/testdb/users01.dbf

output filename=+DGROUPA/testdb/datafile/users.269.793833887 tag=TAG20120912T212155 recid=4 stamp=793833888

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04

Finished backup at 12-SEP-12

RMAN-06497: WARNING: control file is not current, control file autobackup skipped

Starting recover at 13-SEP-12

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/oradata/testdb/redo03.log

archive log filename=/u01/app/oracle/oradata/testdb/redo03.log thread=1 sequence=1

media recovery complete, elapsed time: 00:00:03

Finished recover at 13-SEP-12

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DGROUPA/testdb/datafile/system.266.793833717"

datafile 2 switched to datafile copy "+DGROUPA/testdb/datafile/undotbs1.268.793833879"

datafile 3 switched to datafile copy "+DGROUPA/testdb/datafile/sysaux.267.793833815"

datafile 4 switched to datafile copy "+DGROUPA/testdb/datafile/users.269.793833887"

RMAN> exit

RMAN> **end-of-file**

4. 文件重命名

执行下面这个sql脚本,这里用到的信息都是在第一步中查看的信息。

ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/testdb/redo03.log' TO '+DGOUPA';

ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/testdb/redo02.log' TO '+DGOUPA';

ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/testdb/redo01.log' TO '+DGOUPA';

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE;

ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' DROP;

现在数据库中只有tempfile还留在文件系统中所以我们需要将它删除，但是temp tablespace又不能没有数据文件所以我们需要先创建一个tempfile文件，这个时候创建的tempfile会自动穿件到前面指定的db_create_file_dest='+DGROUPA'参数下面也就是会创建到asm磁盘上。这个时候再把文件系统上的tempfile删除。