oracle遷移至ASM磁碟

標籤：

1、登陸+ASM執行個體查看ASM磁碟是否正常

[[email protected] ~]$ export ORACLE_SID=+ASM
[[email protected] ~]$ sqlplus  / as sysdba

SQL> select name,state from  v$asm_diskgroup;                                                                                                   
NAME                        STATE
---------------           -----------
DG1                         MOUNTED

2、登陸資料庫prod
[[email protected] ~]$ export ORACLE_SID=prod
[[email protected] ~]$ sqlplus / as sysdba

a、查看控制檔案
SQL> show parameter control_files;

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
control_files                        string
/u01/app/oracle/oradata/prod/c
ontrol01.ctl, /u01/app/oracle/
oradata/prod/control02.ctl, /u
01/app/oracle/oradata/prod/con
trol03.ctl
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/control01.ctl
/u01/app/oracle/oradata/prod/control02.ctl
/u01/app/oracle/oradata/prod/control03.ctl



b、查看db_create_file_dest參數
SQL> show parameter db_create_file_dest;

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_create_file_dest                  string


c、修改控制檔案的位置

SQL>  alter system set control_files=‘+DG1‘ scope=spfile;

System altered.

SQL> alter system set db_create_file_dest=‘+DG1‘ scope=spfile;

System altered.


d、關閉資料庫

SQL> shutdown immediate;

3、登陸rman

[[email protected] ~]$ export ORACLE_SID=prod
[[email protected] ~]$ rman target /

a、利用rman遷移目標資料庫控制檔案和資料檔案

RMAN> startup nomount;

b、利用rman將檔案系統上的控制檔案重建控制檔案到ASM磁碟的DG1上
RMAN> restore controlfile from ‘/u01/app/oracle/oradata/prod/control01.ctl‘;

c、利用rman複製資料庫檔案到ASM磁碟組DG1上
RMAN> alter database mount;
RMAN> run{
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 allocate channel c3 type disk;
 allocate channel c4 type disk;
 backup as copy database format ‘+DG1‘;
 }

d、利用RMAN的SWITCH 命令修改控制檔案內資料檔案的指標，使其指向新位置
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;


4、登陸資料庫prod
[[email protected] ~]$ export ORACLE_SID=prod
[[email protected] ~]$ sqlplus / as sysdba

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DG1/prod/controlfile/backup.271.842150617

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DG1/prod/datafile/system.272.842151185
+DG1/prod/datafile/undotbs1.275.842151191
+DG1/prod/datafile/sysaux.273.842151185
+DG1/prod/datafile/users.276.842151211
+DG1/prod/datafile/example.274.842151187

a、遷移temp檔案
SQL> select name,status,enabled from v$tempfile;

NAME
--------------------------------------------------------------------------------
STATUS                ENABLED
--------------------- ------------------------------
/u01/app/oracle/oradata/prod/temp01.dbf
ONLINE                READ WRITE

由於temp檔案沒有可用的資料只是快取資料，temp可以直接添加一個新的temp檔案，然後將老的temp檔案刪除
SQL> alter tablespace temp add tempfile ‘+DG1‘;

Tablespace altered.

SQL> alter tablespace temp drop tempfile ‘/u01/app/oracle/oradata/prod/temp01.dbf‘;

Tablespace altered.


SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DG1/prod/tempfile/temp.279.842151759

b、遷移記錄檔，在DG1建立新的記錄檔，然後將老的檔案刪除

SQL> select group#,member from v$logfile;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         3
/u01/app/oracle/oradata/prod/redo03.log

         2
/u01/app/oracle/oradata/prod/redo02.log

         1
/u01/app/oracle/oradata/prod/redo01.log




SQL> alter database add logfile ‘+DG1‘ size 51m;

Database altered.

SQL> alter database add logfile ‘+DG1‘ size 51m;

Database altered.

SQL> alter database add logfile ‘+DG1‘ size 51m;

Database altered.


刪除的時候需要記錄檔組狀態為inactive狀態，不過刪除不了，說明記錄檔組不是inactive狀態

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

    GROUP# STATUS
---------- ------------------------------------------------
     1 INACTIVE
         2 CURRENT
         3 INACTIVE
         4 INACTIVE
         5 INACTIVE
         6 INACTIVE
SQL> alter database drop logfile ‘/u01/app/oracle/oradata/prod/redo01.log‘;

Database altered.

SQL> alter database drop logfile ‘/u01/app/oracle/oradata/prod/redo02.log‘;
alter database drop logfile ‘/u01/app/oracle/oradata/prod/redo02.log‘
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance prod (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: ‘/u01/app/oracle/oradata/prod/redo02.log‘


SQL> alter database drop logfile ‘/u01/app/oracle/oradata/prod/redo03.log‘;

Database altered.



遇到刪除不了時使用如下命令變更記錄檔檔案狀態，知道可以刪除
SQL> alter system switch logfile;
SQL> alter database drop logfile ‘/u01/app/oracle/oradata/prod/redo02.log‘;

Database altered.

查看記錄檔
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DG1/prod/onlinelog/group_4.280.842152057
+DG1/prod/onlinelog/group_5.281.842152065
+DG1/prod/onlinelog/group_6.282.842152075


查看遷移後的檔案
SQL> select name from v$datafile union select name from v$tempfile union select name from v$controlfile union select member from v$logfile;

NAME
--------------------------------------------------------------------------------
+DG1/prod/controlfile/backup.271.842150617
+DG1/prod/datafile/example.274.842151187
+DG1/prod/datafile/sysaux.273.842151185
+DG1/prod/datafile/system.272.842151185
+DG1/prod/datafile/undotbs1.275.842151191
+DG1/prod/datafile/users.276.842151211
+DG1/prod/onlinelog/group_4.280.842152057
+DG1/prod/onlinelog/group_5.281.842152065
+DG1/prod/onlinelog/group_6.282.842152075
+DG1/prod/tempfile/temp.279.842151759


最後遷移初始化參數檔案
SQL> create pfile from spfile;

File created.

SQL> create spfile=‘+DG1‘ from pfile;

File created.



重啟資料庫查看是否能夠正常啟動

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

Total System Global Area  209715200 bytes
Fixed Size                  1218556 bytes
Variable Size              71305220 bytes
Database Buffers          134217728 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

查看遷移後的檔案
SQL> select name from v$datafile union select name from v$tempfile union select name from v$controlfile union select member from v$logfile;

NAME
--------------------------------------------------------------------------------
+DG1/prod/controlfile/backup.271.842150617
+DG1/prod/datafile/example.274.842151187
+DG1/prod/datafile/sysaux.273.842151185
+DG1/prod/datafile/system.272.842151185
+DG1/prod/datafile/undotbs1.275.842151191
+DG1/prod/datafile/users.276.842151211
+DG1/prod/onlinelog/group_4.280.842152057
+DG1/prod/onlinelog/group_5.281.842152065
+DG1/prod/onlinelog/group_6.282.842152075
+DG1/prod/tempfile/temp.279.842151759

oracle遷移至ASM磁碟