今天將昨天遷移到ASM的資料庫再遷回到DISK上，測試步驟如下：

1.由於RMAN backup時資料庫需在archive狀態，先將資料庫置於archive狀態：

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

2.備份資料庫到asm上。

RMAN> backup database;

Starting backup at 26-FEB-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=137 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DGROUP1/orcl/datafile/system.257.679758645
input datafile fno=00003 name=+DGROUP1/orcl/datafile/sysaux.258.679758711
input datafile fno=00005 name=+DGROUP1/orcl/datafile/example.259.679758737
input datafile fno=00002 name=+DGROUP1/orcl/datafile/undotbs1.260.679758751
input datafile fno=00004 name=+DGROUP1/orcl/datafile/users.261.679758755
channel ORA_DISK_1: starting piece 1 at 26-FEB-09
channel ORA_DISK_1: finished piece 1 at 26-FEB-09
piece handle=+DGROUP2/orcl/backupset/2009_02_26/nnndf0_tag20090226t083524_0.262.679826125

tag=TAG20090226T083524 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 26-FEB-09
channel ORA_DISK_1: finished piece 1 at 26-FEB-09
piece handle=+DGROUP2/orcl/backupset/2009_02_26/ncsnf0_tag20090226t083524_0.261.679826181

tag=TAG20090226T083524 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 26-FEB-09

3.關閉block tracking
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

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

4.修改pfile,啟動到nomount後，從備份中恢復控制檔案

[oracle@rhel131 pfile]$ cat init.ora
orcl.__db_cache_size=188743680
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=88080384
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl',

'/u01/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=286261248
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

#*.control_files='+dgroup1/ORCL/CONTROLFILE/backup.256.679758573'
#*.db_create_file_dest='+dgroup1'
#*.db_recovery_file_dest='+dgroup2'

將control_files，db_create_file_dest和db_recovery_file_dest指向ASM的注消，恢復之前的db_recovery_file_dest

。

用此pfile先啟動到nomount

SQL> startup nomount pfile='/u01/app/oracle/admin/orcl/pfile/init.ora';
ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1219016 bytes
Variable Size 96470584 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes

在RMAN中指定DBID，並恢復controlfile

[oracle@rhel131 pfile]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Feb 26 08:44:44 2009

connected to target database: orcl (not mounted)

RMAN> set DBID=1207536709

executing command: SET DBID

RMAN> restore controlfile from

'+dgroup2/ORCL/BACKUPSET/2009_02_26/ncsnf0_TAG20090226T083524_0.261.679826181';

Starting restore at 26-FEB-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output filename=/u01/app/oracle/oradata/orcl/control01.ctl
output filename=/u01/app/oracle/oradata/orcl/control02.ctl
output filename=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at 26-FEB-09

5.mount起資料庫,恢復資料檔案
SQL> alter database mount;

Database altered.

[oracle@rhel131 pfile]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Feb 26 08:51:06 2009

connected to target database: ORCL (DBID=1207536709, not open)

下面根據 from no-asm to ASM時建立的恢復指令碼來恢復
不過呼叫指令碼的switch all時提示出錯，所以我都改成了switch datafile X。

RMAN> run
2> {
3> set newname for datafile 1 to '/u01/app/oracle/oradata/orcl/system01.dbf' ;
4> set newname for datafile 2 to '/u01/app/oracle/oradata/orcl/undotbs01.dbf' ;
5> set newname for datafile 3 to '/u01/app/oracle/oradata/orcl/sysaux01.dbf' ;
6> set newname for datafile 4 to '/u01/app/oracle/oradata/orcl/users01.dbf' ;
7> set newname for datafile 5 to '/u01/app/oracle/oradata/orcl/example01.dbf' ;
8> restore database;
9> switch datafile 1;
10> switch datafile 2;
11> switch datafile 3;
12> switch datafile 4;
13> switch datafile 5;
14> }

executing command: SET NEWNAME
using target database control file instead of recovery catalog

executing command: SET NEWNAME

Starting restore at 26-FEB-09
Starting implicit crosscheck backup at 26-FEB-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 26-FEB-09

Starting implicit crosscheck copy at 26-FEB-09
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 26-FEB-09

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece

+DGROUP2/orcl/backupset/2009_02_26/nnndf0_tag20090226t083524_0.262.679826125
channel ORA_DISK_1: restored backup piece 1
piece handle=+DGROUP2/orcl/backupset/2009_02_26/nnndf0_tag20090226t083524_0.262.679826125

tag=TAG20090226T083524
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 26-FEB-09

datafile 1 switched to datafile copy
input datafile copy recid=17 stamp=679827296 filename=/u01/app/oracle/oradata/orcl/system01.dbf

datafile 2 switched to datafile copy
input datafile copy recid=19 stamp=679827860 filename=/u01/app/oracle/oradata/orcl/undotbs01.dbf

datafile 3 switched to datafile copy
input datafile copy recid=21 stamp=679827860 filename=/u01/app/oracle/oradata/orcl/sysaux01.dbf

datafile 4 switched to datafile copy
input datafile copy recid=23 stamp=679827860 filename=/u01/app/oracle/oradata/orcl/users01.dbf

datafile 5 switched to datafile copy
input datafile copy recid=25 stamp=679827860 filename=/u01/app/oracle/oradata/orcl/example01.dbf

6.準備開啟資料庫

RMAN> recover database;

Starting recover at 26-FEB-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file +DGROUP1/orcl/onlinelog/group_3.265.679759173
archive log filename=+DGROUP1/orcl/onlinelog/group_3.265.679759173 thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-FEB-09

RMAN> alter database open resetlogs;

database opened

7.最後的工作

將tempfile移過來

SQL> alter tablespace temp01 add tempfile '/u01/app/oracle/oradata/orcl/temp.dbf' size 50m;

Tablespace altered.

SQL> alter tablespace temp01 drop tempfile '+DGROUP1/orcl/tempfile/temp01.266.679759841';

Tablespace altered.

重新redo file

SQL> alter database add logfile group 5
2 '/u01/app/oracle/oradata/orcl/redo05.dbf' size 10m;

Database altered.

SQL> alter database add logfile group 6
2 '/u01/app/oracle/oradata/orcl/redo06.dbf' size 10m;

Database altered.

SQL> alter database add logfile group 7
2 '/u01/app/oracle/oradata/orcl/redo07.dbf' size 10m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select * From v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
2 1 2 52428800 2 YES ACTIVE 489585 26-FEB-09
3 1 1 52428800 2 YES ACTIVE 489296 26-FEB-09
4 1 3 52428800 2 NO ACTIVE 489587 26-FEB-09
5 1 4 10485760 1 NO CURRENT 489592 26-FEB-09
6 1 0 10485760 1 YES UNUSED 0
7 1 0 10485760 1 YES UNUSED 0

6 rows selected.

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

建立spfile

SQL> create spfile from pfile='/u01/app/oracle/admin/orcl/pfile/init.ora';

File created.

也可以刪除ASM的相關檔案，至此ASM的資料庫已完成遷移到普通的DISK上。

ASM的資料庫遷移回到DISK上

相關文章