数据库迁移：文件系统迁至ASM

数据库迁移：文件系统迁至ASM

系统环境：

操作系统：AIX5.3-08

Oracle：  Oracle 10gR2

1、实施操作，选择可行性迁移方案；

2、前期：准备工作，创建ASM磁盘组、启动ASM实例等；

3、中期：完成参数文件、控制文件、数据文件的迁移等；

*****************************************************************************************

*前期目标：配置ASM、创建ASM磁盘组、启动ASM 实例

*****************************************************************************************

:/:<8>volslice list--查看阵列磁盘划分情况

:/:<1>volslice createaix203_asm -z 10gb t01

--从磁盘阵列上划分10g的磁盘空间用以迁移oracle文件系统

#lsdev |grep -i ada  --查找主机的光纤适配卡，用以绑定阵列划分的磁盘

ent2       Available 14-08         10/100 Mbps Ethernet PCI Adapter II(1410ff01)

fcs0       Available 1D-08         FC Adapter

fda0       Available 01-D1         Standard I/O Diskette Adapter

#lscfg -vpl fcs0 --找到光纤适配卡的网络地址

Device Specific.(ZM)........3

NetworkAddress.............10000000C946257E

ROS Level and ID............02881914

:/:<>lun perm lun 10 rwwwn 10000000C946257E

--把新划分的阵列磁盘空间10绑定到光纤适配卡上，权限设为可读可写

#cfgmgr -v

--新添加设备后，不能直接被读取到，需要执行此命令，让其读取ODM库，从而来查找新添加磁盘的配置、驱动信息

--此命令在主机重启时也会自动执行

--此次实施时，手动执行此命令后无效，重启主机后才识别到新添加的阵列设备

#lsdev -c disk  --查看新添加阵列磁盘是否被成功识别，hdisk2为新添加的磁盘设备

hdisk0 Available 1S-08-00-8,0 Other SCSI Disk Drive

hdisk1 Available 1D-08-02     OtherFC SCSI Disk Drive

hdisk2Available 1D-08-02     Other FC SCSI DiskDrive

#

#smit vg  --使用smit命令在新添加的阵列设备上创建卷组oradata，pp大小指定64m

--在新建卷组上创建逻辑卷disk1、disk2、disk3，大小指定3g（即48个pp）

#mklv -y disk1 -t raw oradata 48 hdisk2;

#mklv -y disk2 -t raw oradata 48 hdisk2;

#mklv -y disk3 -t raw oradata 48 hdisk2;

#cd /dev  --进入到设备目录/dev下

#chown oracle:oinstall rdisk*  --为创建的裸设备修改所有者

#chmod 660 rdisk*              --为创建的裸设备修改访问权限

#ls -l | grep rdisk            --查看修改后的裸设备权限

crw-rw----    1 oracle  oinstall     51,  1 May 29 17:43 rdisk1

crw-rw----    1 oracle  oinstall     51,  2 May 29 17:43 rdisk2

crw-rw----    1 oracle  oinstall     51,  3 May 29 17:43 rdisk3

$cd /u01/app/oracle/product/10.2.0/db_1/dbs/--以oracle用户身份到初始化参数路径下

$vi init+ASM.ora   --编辑ASM初始化参数

*.background_dump_dest=‘/u01/app/oracle/admin/+ASM/bdump‘

*.core_dump_dest=‘/u01/app/oracle/admin/+ASM/cdump‘

*.instance_type=‘asm‘

*.large_pool_size=12M

*.remote_login_passwordfile=‘SHARED‘

*.user_dump_dest=‘/u01/app/oracle/admin/+ASM/udump‘

$mkdir -p /u01/app/oracle/admin/+ASM/udump  --创建udump目录，用户进程的追踪目录

$mkdir -p /u01/app/oracle/admin/+ASM/bdump  --创建bdump目录，后台进程的追踪目录

$mkdir -p /u01/app/oracle/admin/+ASM/cdump  --创建cdump目录,内核进程的追踪目录

#cd /u01/app/oracle/product/10.2.0/db_1/bin 

--以root身份到bin目录下执行localconfig脚本，用以启动CSS

#./localconfig delete    --先执行清除脚本

StoppingCSSD.

Unableto communicate with the CSS daemon.

Shutdownhas begun. The daemons should exit soon.

#./localconfig add      --再执行添加脚本

Successfullyaccumulated necessary OCR keys.

CreatingOCR keys for user ‘root‘, privgrp ‘system‘..

Operationsuccessful.

Configurationfor local CSS has been initialized

StaleCSS daemon is running... killing it now

Addingto inittab

Startupwill be queued to init within 30 seconds.

Checkingthe status of new Oracle init process...

Expectingthe CRS daemons to be up within 600 seconds.

CSS isactive on these nodes.

        aix203

CSS isactive on all nodes.

OracleCSS service is installed and running under init(1M)

[oracle@aix203]$export ORACLE_SID=+ASM   --添加ASM实例名

[oracle@aix203]$sqlplus ‘/as sysdba‘

SQL> startup nomount;       --启动ASM实例到nomount

SQL> select instance_name,status from v$instance;  --查询ASM实例状态

INSTANCE_NAME    STATUS

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

+ASM             STARTED

SQL> alter system set ASM_DISKSTRING=‘/dev/rdisk*‘;

--ASM_DISKSTRING参数设置，使ASM可以找到裸设备创建磁盘组

--创建ASM磁盘组为DATA

SQL> create diskgroup DATA normalredundancy

 2  FAILGROUP DATA1 disk‘/dev/rdisk1‘

 3  FAILGROUP DATA2 disk ‘/dev/rdisk2‘

  4 FAILGROUP DATA3 disk ‘/dev/rdisk3‘;

SQL> alter diskgroup DATA check all;          --检查磁盘组的统一性

SQL> select name,state from v$asm_diskgroup;   --查看磁盘组状态，已经mounted

NAME               STATE

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

DATA               MOUNTED

*****************************************************************************************

*中期目标：原库做好冷备、利用RMAN转储文件至ASM（参数文件、控制文件、数据文件）

*****************************************************************************************

--关库，为数据库做一次全备

SQL> create pfile=‘/home/oracle/bak/initmetro.ora‘ from spfile;--备份参数文件

SQL> shutdown immediate;    --关库

SQL> startup mount;         --启库到mount状态,做一次全备

RMAN> backup full database format‘/backup/datafile_bak/level0_%s_%d.bak‘; --备份数据文件

RMAN> backup archivelog all format ‘/backup/arch_bak/arc_%s_%p_%c‘;

--备份归档文件，因之前删除过无用归档，致使有过期归档文件产生

RMAN-00571:===========================================================

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002:failure of backup command at 05/29/2014 15:36:44

RMAN-06059:expected archived log not found, lost of archived log compromisesrecoverability

ORA-19625:error identifying file /arch/1_190_821107190.dbf

ORA-27037:unable to obtain file status

IBM AIXRISC System/6000 Error: 2: No such file or directory

Additionalinformation: 3

RMAN> crosscheck archivelog all; --检查归档的连续性

releasedchannel: ORA_DISK_1

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: sid=132 devtype=DISK

validationfailedfor archived log     

archivelog filename=/arch/1_190_821107190.dbf recid=191 stamp=821547060

RMAN> delete expired archivelog all; --删除过期归档

RMAN> backup archivelog all format ‘/backup/arch_bak/arc_%s_%p_%c‘; 

--再次执行备份归档文件，成功完成

SQL>startup nomount;           --启库到nomount状态

[oracle@aix203]$rman target /  --登录到rman

--做一次冷备，备份到ASM磁盘组

RMAN> run{

2> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

3> ALLOCATE CHANNEL c2 DEVICE TYPE DISK;

4> backup as copy database format=‘+DATA‘;

5> release channel ch1;

6> release channel ch2;   

7> }

RMAN> restore spfile to ‘+DATA/spfilemetro.ora‘;  --转储spfile文件到ASM

SQL>shutdown immediate;

[aix203 oracle:/oracle]vi /u01/app/oracle/product/10.2.0/db_1/dbs/initmetro.ora

--修改$ORACLE_HOME下pfile文件，并将spfile文件重命名，令其使用pfile启动

SPFILE=‘+DATA/spfilemetro.ora‘

SQL> startup mount;                  --此时查看spfile应该为ASM下的spfile启动

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST=‘+DATA‘; --修改数据文件指向路径为ASM

SQL> ALTER SYSTEM SET CONTROL_FILES=‘DATA‘ SCOPE=SPFILE;--修改控制文件指向路径

SQL> startup nomount;                --重启库到nomount状态

RMAN> restore controlfile from‘/u01/app/oracle/oradata/metro/control01.ctl‘;

--此时控制文件中的信息已经指向ASM，用rman完成将控制文件转储到ASM

RMAN> mount database;               --将数据库启动到mount状态

RMAN> switch database to copy;

releasedchannel: ORA_DISK_1

RMAN-00571:===========================================================

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002:failure of switch to copy command at 05/29/2014 18:49:14

RMAN-06571:datafile 1 does not have recoverable copy

--到nomount状态下，重新做一次冷备

RMAN> run{

2> allocate channel c1 device type disk;

3> allocate channel c2 device type disk;

4> backup as copy database format ‘+DATA‘;

5> }

RMAN> mount database;

RMAN> switch database to copy; --再次执行，依旧报错

releasedchannel: ORA_DISK_1

RMAN-00571:===========================================================

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002:failure of switch to copy command at 05/29/2014 18:58:14

RMAN-06571:datafile 1 does not have recoverable copy

--尝试单独备份datafile1，报错依旧

--尝试重新转储控制文件，重新备份，报错依旧

--尝试重新创建磁盘阵列，重新创建卷组、逻辑卷、磁盘组，重新转储参数文件、控制文件、重新备份，报错依旧

--尝试重新创建磁盘阵列，重新规划容错策略，重新转储参数文件、控制文件、重新备份，报错依旧

--尝试使用手工操作将数据文件指向对应ASM，如下操作

SQL> alter database rename file‘+DATA/metro/datafile/system.257.848858337‘ to ‘+DATA/METRO/DATAFILE/SYSTEM.264.848860609‘;    --指向system表空间文件

SQL> alter database rename file‘/u01/app/oracle/oradata/metro/undotbs01.dbf‘ to‘+DATA/METRO/DATAFILE/UNDOTBS1.266.848860809‘;  --指向undo表空间文件

SQL> alter database rename file ‘/u01/app/oracle/oradata/metro/sysaux01.dbf‘to ‘+DATA/METRO/DATAFILE/SYSAUX.263.848860607‘;    --指向sysaux表空间文件

SQL> alter database rename file‘/u01/app/oracle/oradata/metro/users01.dbf‘ to‘+DATA/METRO/DATAFILE/USERS.267.848860815‘;     --指向users表空间文件

SQL> alter database rename file‘/u01/app/oracle/oradata/metro/example01.dbf‘ to‘+DATA/METRO/DATAFILE/EXAMPLE.265.848860723‘;   --指向example表空间文件

SQL> alter database open;   

--启库，因所转储的数据文件为统一的SCN，但与此时日志SCN不符，需做不完全恢复

alter databaseopen

*

ERROR at line 1:

ORA-01190:control file or data file 1 is from before the last RESETLOGS

ORA-01110: data file 1: ‘+DATA/metro/datafile/system.264.848860609‘

SQL> alter database open resetlogs; --提示要做一个不完全恢复

alter databaseopen resetlogs

*

ERROR at line 1:

ORA-01139:RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel; --做一个不完全恢复

ORA-00279:change 862849 generated at 05/29/2014 18:22:44 needed for thread 1

ORA-00289:suggestion : /u01/app/oracle/arch1/1_15_848741495.dbf

ORA-00280:change 862849 for thread 1 is in sequence #15

Specify log:{<RET>=suggested | filename | AUTO | CANCEL}

auto   --自动查找所需日志sequence，未果

ORA-00279:change 863386 generated at 05/29/2014 19:40:54 needed for thread 1

ORA-00289:suggestion : /u01/app/oracle/arch1/1_1_848864454.dbf

ORA-00280:change 863386 for thread 1 is in sequence #1

ORA-00278: logfile ‘/u01/app/oracle/arch1/1_15_848741495.dbf‘ no longer needed

for thisrecovery

ORA-00308:cannot open archived log ‘/u01/app/oracle/arch1/1_1_848864454.dbf‘

ORA-27037:unable to obtain file status

IBM AIX RISCSystem/6000 Error: 2: No such file or directory

Additionalinformation: 3

SQL> recover database until cancel; 

--再次执行不完全恢复，查看到提示需要日志sequence #为1

ORA-00279: change 863386 generated at 05/29/2014 19:06:03 needed forthread 1

ORA-00289: suggestion : /u01/app/oracle/arch1/1_1_848864454.dbf

ORA-00280: change 863386 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

SQL> set linesize 300

SQL> select * from v$log；     --查询sequence #为1的日志组为第2日志组

    GROUP#   THREAD#  SEQUENCE#      BYTES   MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM

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

         1          1          0  52428800          2 YESUNUSED                       0

         3          1          0  52428800          2 YES UNUSED                       0

         2         1          1   52428800          2 NO CURRENT                 86338629-MAY-14

SQL> select * from v$logfile;  --查找到第2日志组中的日志成员

GROUP# STATUS  TYPE    MEMBER                            IS_

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

3       ONLINE          /u01/app/oracle/oradata/metro/redo03.log    NO

2       ONLINE          /u01/app/oracle/oradata/metro/redo02.log    NO

1                                   ONLINE         /u01/app/oracle/oradata/metro/redo01.log    NO

1                                   ONLINE         /disk1/metro/redofile/redo01a.log              NO

2       ONLINE          /disk1/metro/redofile/redo02a.log           NO

3       ONLINE          /disk1/metro/redofile/redo03a.log           NO

--把查找到的对应日志sequence #为1的日志组成员填写至此，即为redo02.log

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

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

Log applied.

Media recovery complete.

SQL> select status fromv$instance;

STATUS

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

MOUNTED

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

SQL> select status fromv$instance;

STATUS

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

OPEN

SQL> select NAME from v$datafile;

NAME

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

+DATA/METRO/DATAFILE/SYSTEM.264.848860609

+DATA/METRO/DATAFILE/UNDOTBS1.266.848860809

+DATA/METRO/DATAFILE/SYSAUX.263.848860607

+DATA/METRO/DATAFILE/USERS.267.848860815

+DATA/METRO/DATAFILE/EXAMPLE.265.848860723

--至此，数据文件转储到ASM

*****************************************************************************************

后期目标：迁移临时表空间、日志文件至ASM

*****************************************************************************************

SQL> select NAME fromv$tempfile;  --查看临时表空间位置

NAME

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

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

SQL> selectname,status,enabled from v$tempfile;   --查看临时表空间状态

NAME                               STATUS   ENABLED

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

/u01/app/oracle/oradata/metro/temp01.dbf  ONLINE   READ WRITE

SQL> alter tablespace tempadd tempfile ‘+DATA‘;  --添加ASM临时表空间

SQL> alter tablespace temp drop tempfile‘/u01/app/oracle/oradata/metro/temp01.dbf‘;--删除原临时表空间

--下面来对日志文件进行迁移

SQL> set linesize 120

SQL> select * from v$log；  --查看当前日志组状态

    GROUP#    THREAD#  SEQUENCE#     BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

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

         1          1          0  52428800          2 YESUNUSED                       0

         2          1          1  52428800          2 NO  CURRENT                 863885 29-MAY-14

         3          1          0  52428800          2 YES UNUSED                       0

--添加3组日志

SQL> alter database addlogfile ‘+DATA‘ SIZE 50m;

SQL> alter database addlogfile ‘+DATA‘ SIZE 50m;

SQL> alter database addlogfile ‘+DATA‘ SIZE 50m;

SQL> selectl.group#,l.sequence#,f.member as name,l.status,l.archived from v$logl,v$logfile f

  2  where f.group#=l.group#

  3 order by 1;--查看当日日志使用状态

    GROUP#  SEQUENCE# NAME                         STATUS           ARC

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

         1          0 /disk1/metro/redofile/redo01a.log         UNUSED           YES

         1          0/u01/app/oracle/oradata/metro/redo01.log   UNUSED           YES

         2          1/disk1/metro/redofile/redo02a.log          CURRENT          NO

         2          1 /u01/app/oracle/oradata/metro/redo02.log   CURRENT          NO

         3          0/disk1/metro/redofile/redo03a.log          UNUSED           YES

         3          0/u01/app/oracle/oradata/metro/redo03.log    UNUSED           YES

         4          0 +DATA/metro/onlinelog/group_4.274.848866UNUSED           YES

                     125

         5          0+DATA/metro/onlinelog/group_5.275.848866 UNUSED           YES

                     171

         6          0+DATA/metro/onlinelog/group_6.276.848866 UNUSED          YES

                     215 

进行多次归档，切换当前日志组，使用‘ alterdatabase drop logfile’命令将原日志组删除，保留新建的ASM日志组，即完成将日志文件迁移到ASM。

SQL> selectl.group#,l.sequence#,f.member as name,l.status,l.archived from v$logl,v$logfile f

 2  where f.group#=l.group#

 3  order by 1;

    GROUP# SEQUENCE# NAME      STATUS           ARC

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

         1          2/disk1/metro/redofile/redo01a.log       INACTIVE         YES

         1          2/u01/app/oracle/oradata/metro/redo01.log INACTIVE         YES

         2          7 /disk1/metro/redofile/redo02a.log        CURRENT          NO

         2          7/u01/app/oracle/oradata/metro/redo02.log CURRENT          NO

         3          3/disk1/metro/redofile/redo03a.log       INACTIVE         YES

         3          3 /u01/app/oracle/oradata/metro/redo03.logINACTIVE         YES

         4          4+DATA/metro/onlinelog/group_4.274.848866 INACTIVE         YES

                      125

         5          5+DATA/metro/onlinelog/group_5.275.848866 INACTIVE         YES

                      171

         6          6+DATA/metro/onlinelog/group_6.276.848866 INACTIVE         YES

                      215

举例：

删除原日志组使用命令：

后续跟进：

        在进行switch命令时报错，后查明发现是一个细节之处做得不对，即向ASM转储数据文件时应处于mount状态，这样才会更新控制文件中的信息，之前多次尝试均是在nomount下做的备份，将文件转储到ASM下的。重新实验，在mount下转储，发现不再报错，问题解决。

数据库迁移：文件系统迁至ASM,古老的榕树,5-wow.com