启动实例
[oracle@qwer dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 20:40:35 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
查看ASM 磁盘组状态
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
+ASM STARTED
SQL> select instance_name,db_name,status from v$asm_client;
no rows selected
SQL> select name,state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
DATA MOUNTED
[grid@qwer ~]$ asmcmd -p lsct
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
+ASM CONNECTED 11.2.0.4.0 11.2.0.4.0 +ASM DATA
Use of uninitialized value $rowval[5] in printf at /u01/app/11.2.0/grid/lib/asmcmdbase.pm line 1734.
PROD CONNECTED 11.2.0.4.0 11.2.0.4.0 PROD
[grid@qwer ~]$ asmcmd -p lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 12281 10715 0 10715 0 N DATA/
磁盘组几乎没使用
迁移控制文件：
01、首先查看控制文件路径
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/PROD/conrol01.ctl,/u01/app/oracle/fast_recovery_area/PROD/control02.ctl
02、修改控制文件路径为ASM磁盘组
SQL> alter system set control_files='+DATA' scope=spfile;
System altered
03、启动RMAN迁移控制文件和数据文件
[oracle@qwer dbs]$ rman target/
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 20:42:00 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
RMAN> restore controlfile from '/u01/app/oracle/oradata/PROD/control01.ctl';
把文件系统上的控制文件还原到到ASM磁盘组DATA上
Starting restore at 19-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/prod/controlfile/current.256.901572305
Finished restore at 19-JAN-16
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> allocate channel c2 device type disk;
5> backup as copy database format'+DATA';
6> }
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=136 device type=DISK
allocated channel: c2
channel c2: SID=135 device type=DISK
Starting backup at 19-JAN-16
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/19/2016 20:49:30
ORA-01507: database not mounted
RMAN> startup mount;
database is already started
database mounted
RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> backup as copy database format '+DATA';
6> }
allocated channel: c1
channel c1: SID=10 device type=DISK
allocated channel: c2
channel c2: SID=136 device type=DISK
Starting backup at 19-JAN-16
channel c1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD/system01.dbf
channel c2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD/sysaux01.dbf
output file name=+DATA/prod/datafile/system.257.901572669 tag=TAG20160119T205109 RECID=1 STAMP=901572674
channel c1: datafile copy complete, elapsed time: 00:00:07
channel c1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD/undotbs01.dbf
output file name=+DATA/prod/datafile/sysaux.258.901572669 tag=TAG20160119T205109 RECID=2 STAMP=901572674
channel c2: datafile copy complete, elapsed time: 00:00:07
channel c2: starting datafile copy
copying current control file
output file name=+DATA/prod/datafile/undotbs1.259.901572677 tag=TAG20160119T205109 RECID=3 STAMP=901572676
channel c1: datafile copy complete, elapsed time: 00:00:01
channel c1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD/users01.dbf
output file name=+DATA/prod/datafile/users.261.901572679 tag=TAG20160119T205109 RECID=5 STAMP=901572678
channel c1: datafile copy complete, elapsed time: 00:00:01
output file name=+DATA/prod/controlfile/backup.260.901572677 tag=TAG20160119T205109 RECID=4 STAMP=901572678
channel c2: datafile copy complete, elapsed time: 00:00:03
Finished backup at 19-JAN-16
released channel: c1
released channel: c2
RMAN> switch database to copy;
用RMAN的SWITCH 命令修改控制文件内数据文件的指针，使其指向新位置
datafile 1 switched to datafile copy "+DATA/prod/datafile/system.257.901572669"
datafile 2 switched to datafile copy "+DATA/prod/datafile/sysaux.258.901572669"
datafile 3 switched to datafile copy "+DATA/prod/datafile/undotbs1.259.901572677"
datafile 4 switched to datafile copy "+DATA/prod/datafile/users.261.901572679"
RMAN> recover database;
Starting recover at 19-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-JAN-16
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
迁移tempfile到ASM磁盘组DATA上
[oracle@qwer dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 20:53:43 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
PROD OPEN
SQL> select name,status,enabled from v$tempfile;
NAME
--------------------------------------------------------------------------------
STATUS ENABLED
------- ----------
/u01/app/oracle/oradata/PROD/temp01.dbf
ONLINE READ WRITE
SQL> alter tablespace temp add tempfile '+DATA';
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/PROD/temp01.dbf';
Tablespace altered.
SQL> select name,status,enabled from v$tempfile;
NAME
--------------------------------------------------------------------------------
STATUS ENABLED
------- ----------
+DATA/prod/tempfile/temp.262.901573007
ONLINE READ WRITE
迁移日志文件到ASM磁盘
SQL> select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#;
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
3 3
/u01/app/oracle/oradata/PROD/redo03.log
INACTIVE YES
2 5
/u01/app/oracle/oradata/PROD/redo02.log
CURRENT NO
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
1 4
/u01/app/oracle/oradata/PROD/redo01.log
INACTIVE YES
SQL> alter database add logfile '+DATA' size 10M;
Database altered.
SQL> alter database add logfile '+DATA' size 10M;
Database altered.
SQL> alter database add logfile '+DATA' size 10M;
Database altered.
SQL> alter database drop logfile '/u01/app/oracle/oradata/PROD/redo03.log';
Database altered.
SQL> alter database drop logfile '/u01/app/oracle/oradata/PROD/redo01.log';
Database altered.
SQL> select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#;
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
2 5
/u01/app/oracle/oradata/PROD/redo02.log
CURRENT NO
4 0
+DATA/prod/onlinelog/group_4.263.901573607
UNUSED YES
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
5 0
+DATA/prod/onlinelog/group_5.264.901573623
UNUSED YES
6 0
+DATA/prod/onlinelog/group_6.265.901573627
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
UNUSED YES
SQL> alter system switch logfile;
System altered.
SQL> select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#;
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
2 5
/u01/app/oracle/oradata/PROD/redo02.log
ACTIVE YES
4 6
+DATA/prod/onlinelog/group_4.263.901573607
CURRENT NO
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
5 0
+DATA/prod/onlinelog/group_5.264.901573623
UNUSED YES
6 0
+DATA/prod/onlinelog/group_6.265.901573627
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
UNUSED YES
SQL> alter system checkpoint;
System altered.
SQL> select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#;
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
2 5
/u01/app/oracle/oradata/PROD/redo02.log
INACTIVE YES
4 6
+DATA/prod/onlinelog/group_4.263.901573607
CURRENT NO
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
5 0
+DATA/prod/onlinelog/group_5.264.901573623
UNUSED YES
6 0
+DATA/prod/onlinelog/group_6.265.901573627
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
UNUSED YES
SQL> alter database drop logfile group 2;
Database altered.
SQL> select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#;
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
4 6
+DATA/prod/onlinelog/group_4.263.901573607
CURRENT NO
5 0
+DATA/prod/onlinelog/group_5.264.901573623
UNUSED YES
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
6 0
+DATA/prod/onlinelog/group_6.265.901573627
UNUSED YES
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select l.group#,l.sequence#,f.member as name,l.status,l.archived from v$log l,v$logfile f where f.group#=l.group#;
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
4 6
+DATA/prod/onlinelog/group_4.263.901573607
ACTIVE YES
5 7
+DATA/prod/onlinelog/group_5.264.901573623
ACTIVE YES
GROUP# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
STATUS ARC
---------------- ---
6 8
+DATA/prod/onlinelog/group_6.265.901573627
CURRENT NO
迁移参数文件到ASM磁盘
[oracle@qwer ~]$ su - grid
Password:
[grid@qwer ~]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd DATA
ASMCMD> ls
ASM/
PROD/
ASMCMD> cd PROD
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
ASMCMD> cd DATAFILE
ASMCMD> ls
SYSAUX.258.901572669
SYSTEM.257.901572669
UNDOTBS1.259.901572677
USERS.261.901572679
ASMCMD> cd ..
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
ASMCMD> cd ..
ASMCMD> ls
ASM/
PROD/
ASMCMD> cd PROD
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
ASMCMD> mkdir parameter
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
parameter/
ASMCMD> cd parameter
ASMCMD> ls
ASMCMD> pwd
+DATA/PROD/parameter
ASMCMD> ls
ASMCMD> cp /u01/app/oracle/product/11.2.0//dbhome_1/dbs/spfilePROD.ora
usage: cp [--service ] [--port ]
[connect_str:]src_file [connect_str:]tgt_file
help: help cp
ASMCMD> cp /u01/app/oracle/product/11.2.0//dbhome_1/dbs/spfilePROD.ora +DATA/PROD/parameter/spfilePROD.ora
copying /u01/app/oracle/product/11.2.0//dbhome_1/dbs/spfilePROD.ora -> +DATA/PROD/parameter/spfilePROD.ora
ASMCMD> ls
spfilePROD.ora
ASMCMD> cd ..
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
parameter/
或者create spfile='+data' from pfile;然后在原$oracle_home/dbs下mv原动态参数文件，mv spfilePROD.ora spfilePROD.ora.bak
并且编辑原静态参数文件加上spfilePROD.ora=‘+data/prod/parameter/spfileprod.ora'
或者create spfile='+data' from pfile;然后在原$oracle_home/dbs下mv原动态参数文件，mv spfilePROD.ora spfilePROD.ora.bak
并且编辑原静态参数文件加上spfilePROD.ora=‘+data/prod/parameter/spfileprod.ora'
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30604784/viewspace-2051336/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
