四、用rman從檔案系統遷移資料庫到asm

1、備份資料庫
[oracle@haozg dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 5 14:12:57 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11 (DBID=729090162)

RMAN> run{
 allocate channel c1 type disk maxpiecesize=500m;
 backup current controlfile format '/oracle/backup/ctl_%d_%s';
 backup full database format '/oracle/backup/db_%d_%s_%p_%t';
 sql 'alter system archive log current';
 release channel c1;
 }2> 3> 4> 5> 6> 7>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=36 device type=DISK

Starting backup at 05-JUN-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 05-JUN-12
channel c1: finished piece 1 at 05-JUN-12
piece handle=/oracle/backup/ctl_ORA11_1 tag=TAG20120605T141421 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-JUN-12

Starting backup at 05-JUN-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/oradata/ora11/system01.dbf
input datafile file number=00002 name=/oracle/oradata/ora11/sysaux01.dbf
input datafile file number=00003 name=/oracle/oradata/ora11/undotbs01.dbf
input datafile file number=00004 name=/oracle/oradata/ora11/users01.dbf
channel c1: starting piece 1 at 05-JUN-12
channel c1: finished piece 1 at 05-JUN-12
piece handle=/oracle/backup/db_ORA11_2_1_785168069 tag=TAG20120605T141429 comment=NONE
channel c1: starting piece 2 at 05-JUN-12
channel c1: finished piece 2 at 05-JUN-12
piece handle=/oracle/backup/db_ORA11_2_2_785168069 tag=TAG20120605T141429 comment=NONE
channel c1: starting piece 3 at 05-JUN-12
channel c1: finished piece 3 at 05-JUN-12
piece handle=/oracle/backup/db_ORA11_2_3_785168069 tag=TAG20120605T141429 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:09
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 05-JUN-12
channel c1: finished piece 1 at 05-JUN-12
piece handle=/oracle/backup/db_ORA11_3_1_785168198 tag=TAG20120605T141429 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-JUN-12

sql statement: alter system archive log current

released channel: c1

RMAN>

2、修改控制檔案位置引數
SQL> show parameter control_file;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /oracle/oradata/ora11/control0
                                                 1.ctl, /oracle/flash_recovery_
                                                 area/ora11/control02.ctl

SQL> alter system set control_files='+DGASM/controlfile/control01.ctl' scope=spfile;

System altered.

3、恢復控制檔案  在nomount 下

注意oracle 使用者必須在asmadmin、asmdba、等grid使用者所在組，否則在轉儲控制檔案會提示許可權不足

RMAN> restore controlfile  from '/oracle/oradata/ora11/control01.ctl';

Starting restore at 05-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DGASM/controlfile/control01.ctl
Finished restore at 05-JUN-12

RMAN>

4、RMAN copy資料庫到ASM

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

backup as copy database format '+DGASM';

RMAN> backup as copy database format '+DGASM';

Starting backup at 05-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle/oradata/ora11/system01.dbf
output file name=+DGASM/ora11/datafile/system.257.785186755 tag=TAG20120605T192553 RECID=2 STAMP=785186834
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:29
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/oradata/ora11/sysaux01.dbf
output file name=+DGASM/ora11/datafile/sysaux.258.785186845 tag=TAG20120605T192553 RECID=3 STAMP=785186894
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:57
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oracle/oradata/ora11/undotbs01.dbf
output file name=+DGASM/ora11/datafile/undotbs1.259.785186901 tag=TAG20120605T192553 RECID=4 STAMP=785186908
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DGASM/ora11/controlfile/backup.260.785186917 tag=TAG20120605T192553 RECID=5 STAMP=785186918
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oracle/oradata/ora11/users01.dbf
output file name=+DGASM/ora11/datafile/users.261.785186921 tag=TAG20120605T192553 RECID=6 STAMP=785186920
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 05-JUN-12
channel ORA_DISK_1: finished piece 1 at 05-JUN-12
piece handle=+DGASM/ora11/backupset/2012_06_05/nnsnf0_tag20120605t192553_0.262.785186923 tag=TAG20120605T192553 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-JUN-12

RMAN>

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/ora11/system01.dbf
/oracle/oradata/ora11/sysaux01.dbf
/oracle/oradata/ora11/undotbs01.dbf
/oracle/oradata/ora11/users01.dbf

SQL>

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DGASM/ora11/datafile/system.257.785186755"
datafile 2 switched to datafile copy "+DGASM/ora11/datafile/sysaux.258.785186845"
datafile 3 switched to datafile copy "+DGASM/ora11/datafile/undotbs1.259.785186901"
datafile 4 switched to datafile copy "+DGASM/ora11/datafile/users.261.785186921"

RMAN>

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DGASM/ora11/datafile/system.257.785186755
+DGASM/ora11/datafile/sysaux.258.785186845
+DGASM/ora11/datafile/undotbs1.259.785186901
+DGASM/ora11/datafile/users.261.785186921

SQL>

RMAN> alter database open;

database opened

5、切換日誌檔案 在open下

alter database drop logfile group 1;

Alter database add logfile group 1 ('+dgasm/ora11/redofile') size 50m;

alter database rename file '/oracle/oradata/ora11/redo01.log' to '+dgasm/ora11/redofile';

alter database rename file '/oracle/oradata/ora11/redo02.log' to '+DGASM/redofile';

alter database rename file '/oracle/oradata/ora11/redo03.log' to '+DGASM/redofile';

alter database drop logfile group 1;

Alter database add logfile group 1 ('+dgasm/ora11/redo') size 50m;

 

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL> alter database drop logfile group 1;

Database altered.

SQL>  Alter database add logfile group 1 ('+dgasm') size 50m;

Database altered.

SQL>

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 ('+dgasm') size 50m;

Database altered.

SQL>

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 CURRENT
         3 ACTIVE

SQL>

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 ('+dgasm') size 50m;

Database altered.

SQL>
SQL>
SQL>  select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL>  select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 INACTIVE
         3 CURRENT

SQL>
SQL> desc v$logfile;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP#                                             NUMBER
 STATUS                                             VARCHAR2(7)
 TYPE                                               VARCHAR2(7)
 MEMBER                                             VARCHAR2(513)
 IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DGASM/ora11/onlinelog/group_3.265.785192859
+DGASM/ora11/onlinelog/group_2.264.785192615
+DGASM/ora11/onlinelog/group_1.263.785192081

SQL>

6、遷移引數檔案spfile

SQL> create spfile='+dgasm' from pfile;

File created.

[oracle@haozg dbs]$ rm -rf initora11.ora
[oracle@haozg dbs]$ rm -rf spfileora11.ora
[oracle@haozg dbs]$ vi initora11.ora
spfile='+dgasm/DB_UNKNOWN/PARAMETERFILE/SPFILE.266.785194197'

[oracle@haozg dbs]$ ls
hc_DBUA0.dat  init.ora       initora11.ora.bak  orapwora11     peshm_ora11_0   spfileora11.ora.bak
hc_ora11.dat  initora11.ora  lkORA11            peshm_DBUA0_0  snapcf_ora11.f
[oracle@haozg dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 5 21:33:23 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
Database mounted.
Database opened.
SQL>

7、修改歸檔位置

略

8、增加temp檔案

alter tablespace temp add tempfile '+dgasm';

alter tablespace temp drop tempfile '/oracle/oradata/ora11/temp01.dbf';

過程如下：

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/ora11/temp01.dbf

SQL> alter tablespace temp add tempfile '+dgasm';

Tablespace altered.

SQL> alter tablespace temp drop tempfile '/oracle/oradata/ora11/temp01.dbf';

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DGASM/ora11/tempfile/temp.267.785196481

SQL>
SQL>

到此完成資料庫的遷移，歸檔就不做修改了