使用rman copy将数据库移植到ASM存储区

最新推荐文章于 2022-07-14 23:08:56 发布

cnk45946

最新推荐文章于 2022-07-14 23:08:56 发布

阅读量115

点赞数

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

一描述

本实验环境根据043管理课件II”将数据库移植到ASM存储区”设计.

二操作环境

2.1 OS info

$lsb_release -a
LSB Version:    :core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: EnterpriseEnterpriseServer
Description:    Enterprise Linux Enterprise Linux Server release 5.6 (Carthage)
Release:        5.6
Codename:       Carthage
$uname -a
Linux stu00 2.6.18-238.el5 #1 SMP Tue Jan 4 15:24:05 EST 2011 i686 i686 i386 GNU/Linux
$

2.2 DB info

sqlplus / as sysdba <

set lines 150
COL PRODUCT FORMAT A55
COL VERSION FORMAT A15
COL STATUS FORMAT A15 
SELECT * FROM PRODUCT_COMPONENT_VERSION;
archive log list;
EOF

2.3 other

$export ORACLE_SID=+ASM
$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 6 00:16:26 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area   83886080 bytes
Fixed Size                  1265912 bytes
Variable Size              57454344 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> exit

$ps -ef |grep -E "ora_|asm_"
oracle    4073     1 0 00:52 ?        00:00:00 asm_pmon_+ASM
oracle    4075     1 0 00:52 ?        00:00:00 asm_psp0_+ASM
oracle    4077     1 0 00:52 ?        00:00:00 asm_mman_+ASM
oracle    4079     1 0 00:52 ?        00:00:00 asm_dbw0_+ASM
oracle    4081     1 0 00:52 ?        00:00:00 asm_lgwr_+ASM
oracle    4083     1 0 00:52 ?        00:00:00 asm_ckpt_+ASM
oracle    4085     1 0 00:52 ?        00:00:00 asm_smon_+ASM
oracle    4087     1 0 00:52 ?        00:00:00 asm_rbal_+ASM
oracle    4089     1 0 00:52 ?        00:00:00 asm_gmon_+ASM
oracle    4262     1 0 01:01 ?        00:00:00 ora_pmon_testb
oracle    4264     1 0 01:01 ?        00:00:00 ora_psp0_testb
oracle    4266     1 0 01:01 ?        00:00:00 ora_mman_testb
oracle    4268     1 0 01:01 ?        00:00:00 ora_dbw0_testb
oracle    4270     1 0 01:01 ?        00:00:00 ora_lgwr_testb
oracle    4272     1 0 01:01 ?        00:00:00 ora_ckpt_testb
oracle    4274     1 0 01:01 ?        00:00:00 ora_smon_testb
oracle    4276     1 0 01:01 ?        00:00:00 ora_reco_testb
oracle    4278     1 0 01:01 ?        00:00:00 ora_cjq0_testb
oracle    4280     1 0 01:01 ?        00:00:00 ora_mmon_testb
oracle    4282     1 0 01:01 ?        00:00:00 ora_mmnl_testb
oracle    4284     1 0 01:01 ?        00:00:00 ora_d000_testb
oracle    4286     1 0 01:01 ?        00:00:00 ora_s000_testb
oracle    4292     1 0 01:02 ?        00:00:01 ora_j000_testb
oracle    4381 4193 0 01:08 pts/1    00:00:00 grep -E ora_|asm_
$

(注: ASM实例磁盘组名 ASMD )

三过程设计

3.1 使用v$controlfile,v$logfile和v$tempfile获取当前控制文件,redolog和临时文件的文件名.
3.2 编辑源数据库参数文件,设置flashback area.去掉控制文件参数.
3.3 备份控制文件.
3.4 关闭数据库
3.5 编辑参数文件,编辑rman copy过程

步骤顺序

Rman

SQLPLUS

run{

startup nomount pfile ='/home/oracle/pfiletestb_asm.ora';

restore controlfile from '/home/oracle/control_bk_asm.ctl';

alter database mount;

backup as copy database format '+ASMD';

recover database;

}

switch database to copy;

alter database rename file '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log' to '+ASMD' ;

alter database rename file '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log' to '+ASMD' ;

alter database rename file '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01.log' to '+ASMD' ;

alter database open resetlogs;

alter tablespace temp add tempfile ;

alter database tempfile '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/temp01.dbf' DROP

3.6 善后(controlfile,spfile)

四详细步骤操作

4.1 查询控制文件,日志文件,与临时数据文件位置名称
SQL> show parameter spfi

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0/db_1/dbs/spfiletestb.ora
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/oradata/testb/control01.ctl
/u01/app/oracle/product/10.2.0/db_1/oradata/testb/control02.ctl
/u01/app/oracle/product/10.2.0/db_1/oradata/testb/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log
/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log
/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01.log

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/oradata/testb/temp01.dbf

4.2 编辑源数据库参数文件(修改参数前注意对参数文件的备份,以备回滚时使用,备份语句略),将参数文件中相应目录参数修改到'+ASM'存储区
SQL> show parameter reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0

SQL> alter system set db_recovery_file_dest_size=30G;

System altered.

SQL> alter system set db_recovery_file_dest='+ASMD';

System altered.

SQL> show parameter crea

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
db_create_file_dest                  string
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
SQL> alter system set db_create_file_dest='+ASMD';

System altered.

SQL> alter system set db_create_online_log_dest_1='+ASMD';

System altered.

SQL> create pfile ='/home/oracle/pfiletestb_asm.ora' from spfile;

File created.

SQL>

4.3 备份控制文件
SQL> alter database backup controlfile to '/home/oracle/control_bk_asm.ctl';

Database altered.

SQL>
4.4 关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

4.5 编辑参数文件,去掉controlfile参数.(cat 列出已经编辑好的参数文件内容),参数文件编辑好后进入rman运行backup copy命令.
$cat pfiletestb_asm.ora
testb.__db_cache_size=192937984
testb.__java_pool_size=4194304
testb.__large_pool_size=4194304
testb.__shared_pool_size=79691776
testb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/product/10.2.0/db_1/admin/testb/adump'
*.background_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/testb/bdump'
*.compatible='10.2.0.3.0'
*.core_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/testb/cdump'
*.db_block_size=8192
*.db_create_file_dest='+ASMD'
*.db_create_online_log_dest_1='+ASMD'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='testb'
*.db_recovery_file_dest_size=32212254720
*.db_recovery_file_dest='+ASMD'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testbXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/testb/udump'

/*进入rman,运行前期设计中的rman命令,backup copy并切换数据库到ASM存储上. */
$export ORACLE_SID=testb
$rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jul 6 01:19:00 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database (not started)

RMAN> run{
startup nomount pfile ='/home/oracle/pfiletestb_asm.ora';
restore controlfile from '/home/oracle/control_bk_asm.ctl';
alter database mount;
backup as copy database format '+ASMD';
recover database;
}
switch database to copy;
2> 3> 4> 5> 6> 7>
Oracle instance started

Total System Global Area     285212672 bytes

Fixed Size                     1267068 bytes
Variable Size                 88083076 bytes
Database Buffers             192937984 bytes
Redo Buffers                   2924544 bytes

Starting restore at 2012/07/06 01:19:38
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+ASMD/testb/controlfile/current.258.787886385
Finished restore at 2012/07/06 01:19:54

database mounted
released channel: ORA_DISK_1

Starting backup at 2012/07/06 01:20:01
Starting implicit crosscheck backup at 2012/07/06 01:20:01
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 2012/07/06 01:20:02

Starting implicit crosscheck copy at 2012/07/06 01:20:02
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2012/07/06 01:20:02

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/product/10.2.0/db_1/oradata/testb/system01.dbf
output filename=+ASMD/testb/datafile/system.267.787886403 tag=TAG20120706T012002 recid=2 stamp=787886510
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/product/10.2.0/db_1/oradata/testb/sysaux01.dbf
output filename=+ASMD/testb/datafile/sysaux.268.787886521 tag=TAG20120706T012002 recid=3 stamp=787886558
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/product/10.2.0/db_1/oradata/testb/undotbs01.dbf
output filename=+ASMD/testb/datafile/undotbs1.269.787886565 tag=TAG20120706T012002 recid=4 stamp=787886581
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/product/10.2.0/db_1/oradata/testb/example01.dbf
output filename=+ASMD/testb/datafile/example.256.787886589 tag=TAG20120706T012002 recid=5 stamp=787886612
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/product/10.2.0/db_1/oradata/testb/users01.dbf
output filename=+ASMD/testb/datafile/users.261.787886615 tag=TAG20120706T012002 recid=6 stamp=787886616
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2012/07/06 01:23:38

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

Starting recover at 2012/07/06 01:23:39
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 9 is already on disk as file /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log
archive log filename=/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 2012/07/06 01:23:44

RMAN>
datafile 1 switched to datafile copy "+ASMD/testb/datafile/system.267.787886403"
datafile 2 switched to datafile copy "+ASMD/testb/datafile/undotbs1.269.787886565"
datafile 3 switched to datafile copy "+ASMD/testb/datafile/sysaux.268.787886521"
datafile 4 switched to datafile copy "+ASMD/testb/datafile/users.261.787886615"
datafile 5 switched to datafile copy "+ASMD/testb/datafile/example.256.787886589"

RMAN>

/* rman备份切换完成后,以sqlplus工具登录数据库完成步骤设计中的sqlplus的内容工作(在rman里面,用SQL前缀的方式执行下面的我没有执行成功 ... ... 悲) */
RMAN> exit

Recovery Manager complete.

$export ORACLE_SID=testb
$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 6 01:58:03 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log' to '+ASMD' ;

alter database rename file '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log' to '+ASMD' ;

alter database rename file '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01.log' to '+ASMD' ;

alter database open resetlogs;

alter tablespace temp add tempfile ;

alter database tempfile '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/temp01.dbf' DROP;
Database altered.

SQL> SQL>
Database altered.

SQL> SQL>
Database altered.

SQL> SQL>

Database altered.

SQL> SQL>

Tablespace altered.

SQL> SQL>
Database altered.

SQL> SQL>
SQL>
/* 至此移植到ASM存储区完成*/

4.6 善后工作
从之前的步骤中可以知道,目前数据库的参数文件为pfile,且pfile中的controlfile参数未进行设置,下面对相应内容进行补充.
SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +ASMD/testb/controlfile/current.271.787901027
SQL>
/*将新 control_files 值添加到 /home/oracle/pfiletestb_asm.ora 参数文件中 ( 过程略 ). 向 /home/oracle/pfiletestb_asm.ora 文件添加完 control_files 后 , 创建 spfile 从 /home/oracle/pfiletestb_asm.ora 文件中 , 关闭重启数据库以使用 spfile. */
$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 6 05:36:13 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create spfile from pfile='/home/oracle/pfiletestb_asm.ora';

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size                  1267068 bytes
Variable Size              88083076 bytes
Database Buffers          192937984 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfiletestb.ora
SQL>

至此,数据库移植到ASM完善完成.