云计算服务
服务器租用
DDOS 防护
虚拟主机
域名服务
基础设施
关于我们
文件系统怎样迁移到ASM，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
数据库全备 仅仅是一个databse的全备，没有任何额外的附加功能，仅0 级 增量备份 既是数据库的全备，又是，0级增量备份，在此基础上，可以后续执行增量备份 结论 因此推荐使用0级增量备份做数据库的全备
最好是以grid用户进入asm 环境
[oracle@sink ~]$ su - grid
Password:
[grid@sink ~]$ asmcmd
做完 0 级 增量备份后的备份文件
ASMCMD> pwd
+data/sink
ASMCMD> ls
BACKUPSET/
CONTROLFILE/
DATAFILE/
PARAMETERFILE/
查看刚才做的 0级增量 备份
RMAN> list copy of database;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
9 1 A 06-JAN-18 1218041 06-JAN-18
Name: +DATA/sink/datafile/system.258.964712643
Tag: ORA_ASM_MIGRATION
8 2 A 06-JAN-18 1218040 06-JAN-18
Name: +DATA/sink/datafile/sysaux.257.964712643
Tag: ORA_ASM_MIGRATION
4 3 A 06-JAN-18 1218045 06-JAN-18
Name: +DATA/sink/datafile/undotbs1.261.964712645
Tag: ORA_ASM_MIGRATION
6 4 A 06-JAN-18 1218050 06-JAN-18
Name: +DATA/sink/datafile/users.263.964712657
Tag: ORA_ASM_MIGRATION
7 5 A 06-JAN-18 1218043 06-JAN-18
Name: +DATA/sink/datafile/example.260.964712643
Tag: ORA_ASM_MIGRATION
3 6 A 06-JAN-18 1218042 06-JAN-18
Name: +DATA/sink/datafile/tbssss.259.964712643
Tag: ORA_ASM_MIGRATION
RMAN>
转储spfile到
RMAN> restore spfile to '+DATA/spfilesink.ora';
Starting restore at 06-JAN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/spfilesink.ora
channel ORA_DISK_1: reading from backup piece +DATA/sink/backupset/2018_01_06/nnsnn0_ora_asm_migration_0.264.964712657
channel ORA_DISK_1: piece handle=+DATA/sink/backupset/2018_01_06/nnsnn0_ora_asm_migration_0.264.964712657 tag=ORA_ASM_MIGRATION
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 06-JAN-18
查看转储spfile参数文件后的 在ASM中的 路径状态
ASMCMD> pwd
+data
ASMCMD> ls
ASM/
SINK/
spfilesink.ora
ASMCMD>
修改并查看pfile中的内容，使其内容指向ASM中spfile的spfilesink.ora的路径，有意思！
[oracle@sink dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@sink dbs]$ ls
afiedt.buf hc_test.dat initsink.ora lkSINK orapwsink snapcf_sink.f
hc_sink.dat init.ora inittest.ora lkTEST orapwtest spfilesink.ora
[oracle@sink dbs]$ vim initsink.ora
[oracle@sink dbs]$ cat initsink.ora
spfile='+DATA/spfilesink.ora'
[oracle@sink dbs]$
再次进入SQL环境，启动到nomount，设置spfile路径指向，并查看相关参数是否操作生效！成功了！
[oracle@sink dbs]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 16:30:19 2018
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
16:30:19 SYS @ sink >startup force nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 553651280 bytes
Database Buffers 276824064 bytes
Redo Buffers 2371584 bytes
16:30:38 SYS @ sink >alter system set db_create_file_dest='+DATA';
System altered.
Elapsed: 00:00:00.01
16:31:23 SYS @ sink >show parameter create;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string +DATA
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
16:31:33 SYS @ sink >
自上一步之后，查看数据库启动状态，然后satrtup force，将他启动到opened状态
16:31:33 SYS @ sink >select status from v$instance;
STATUS
------------
STARTED
1 row selected.
Elapsed: 00:00:00.00
16:35:24 SYS @ sink >alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted
Elapsed: 00:00:00.00
16:35:39 SYS @ sink >startup force;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 553651280 bytes
Database Buffers 276824064 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.
16:36:24 SYS @ sink >
oracle启动到某一状态的时候 之后启动的情况 satrtup nomountalter database mount alter database open startup mount alter database open -------------------------- startup (open) ---------------------------- -------------------------- oracle启动到某一状态之后，只能一步一步的启动，直至open状态，不能越级实行后续启动
这里查询一下数据文件的file#和name，方便之后的数据文件迁移
SYS @ sink >r
1* select file#,name from v$datafile
FILE# NAME
---------- -------------------------------------------------------
1 /u01/app/oracle/oradata/sink/system01.dbf
2 /u01/app/oracle/oradata/sink/sysaux01.dbf
3 /u01/app/oracle/oradata/sink/undotbs01.dbf
4 /u01/app/oracle/oradata/sink/users01.dbf
5 /u01/app/oracle/oradata/sink/example01.dbf
6 +DATA/sink/datafile/tbssss.256.963504823
6 rows selected.
Elapsed: 00:00:00.01
转储控制文件失败，看错误信息提示：意思是在数据库是mount或者open执行这条语句没有使用TO语句，
RMAN> restore controlfile from '/u01/app/oracle/oradata/sink/control01.ctl';
Starting restore at 06-JAN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/06/2018 16:45:57
RMAN-06496: must use the TO clause when the database is mounted or open
这次加了to语句还是不行，后来纠结了一阵子，发现转储控制文件发现必须要oralce处于nomount状态
RMAN> restore controlfile from '/u01/app/oracle/oradata/sink/control01.ctl' to '+data/';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "to": expecting one of: "archivelog, channel, check, controlfile, database, datafile, device, force, from, high, preview, primary, skip readonly, spfile, standby, tablespace, to restore point, until restore point, until, validate, (, ;"
RMAN-01007: at line 1 column 71 file: standard input
找到头绪之后，将数据库启动到nomount状态
[oracle@sink dbs]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 16:48:57 2018
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
16:48:57 SYS @ sink >select status from v$instance;
STATUS
------------
OPEN
1 row selected.
Elapsed: 00:00:00.01
16:49:10 SYS @ sink >startup nomount;
ORA-01081: cannot start already-running ORACLE - shut it down first
16:49:17 SYS @ sink >startup force nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 553651280 bytes
Database Buffers 276824064 bytes
Redo Buffers 2371584 bytes
16:49:33 SYS @ sink >select status from v$instance;
STATUS
------------
STARTED
1 row selected.
Elapsed: 00:00:00.01
16:49:57 SYS @ sink >
好了，经过一翻纠结，终于成功了，控制文件转储成功！
RMAN> restore controlfile from '/u01/app/oracle/oradata/sink/control01.ctl';
Starting restore at 06-JAN-18
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=+DATA/sink/controlfile/current.266.964717197
output file name=+DATA/sink/controlfile/current.267.964717197
Finished restore at 06-JAN-18
RMAN>
查看控制文件迁移后的效果
ASMCMD> pwd
+data/sink/controlfile
ASMCMD> ls
Backup.262.964712653
current.266.964717197
current.267.964717197
在RMAN环境中将数据库切到mount状态，switch...修改控制文件用datafile copy做当前DB的datafile使用
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/sink/datafile/system.258.964712643"
datafile 2 switched to datafile copy "+DATA/sink/datafile/sysaux.257.964712643"
datafile 3 switched to datafile copy "+DATA/sink/datafile/undotbs1.261.964712645"
datafile 4 switched to datafile copy "+DATA/sink/datafile/users.263.964712657"
datafile 5 switched to datafile copy "+DATA/sink/datafile/example.260.964712643"
datafile 6 switched to datafile copy "+DATA/sink/datafile/tbssss.259.964712643"
直接exit退出rman环境，然后直接!sql进入SQL环境，注意数据库状态仍然为mount，查看dataifle的路径
RMAN> exit
Recovery Manager complete.
[oracle@sink dbs]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 17:42:31 2018
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
17:42:41 SYS @ sink >col name for a55
17:42:57 SYS @ sink >r
1* select name from v$datafile
NAME
-------------------------------------------------------
+DATA/sink/datafile/system.258.964712643
+DATA/sink/datafile/sysaux.257.964712643
+DATA/sink/datafile/undotbs1.261.964712645
+DATA/sink/datafile/users.263.964712657
+DATA/sink/datafile/example.260.964712643
+DATA/sink/datafile/tbssss.259.964712643
6 rows selected.
Elapsed: 00:00:00.01
17:42:58 SYS @ sink >
recover databse（应用 ）开多个channel加快recover速度，生产DB很大，恢复时间很长，这是不错的办法
RMAN> run{
2> allocate channel dev1 device type disk;
3> allocate channel dev2 device type disk;
4> allocate channel dev3 device type disk;
5> allocate channel dev4 device type disk;
6> recover database;
7> }
released channel: ORA_DISK_1
allocated channel: dev1
channel dev1: SID=25 device type=DISK
allocated channel: dev2
channel dev2: SID=26 device type=DISK
allocated channel: dev3
channel dev3: SID=27 device type=DISK
allocated channel: dev4
channel dev4: SID=28 device type=DISK
Starting recover at 06-JAN-18
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-JAN-18
released channel: dev1
released channel: dev2
released channel: dev3
released channel: dev4
RMAN>
exit，进入sql环境，打开到open，看临时文件name，临时表空间name，
RMAN> exit
Recovery Manager complete.
[oracle@sink dbs]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 17:57:16 2018
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
17:59:16 SYS @ sink >alter database open;
Database altered.
Elapsed: 00:00:02.44
17:59:50 SYS @ sink >select name from v$tempfile;
NAME
-------------------------------------------------------
/u01/app/oracle/oradata/sink/temp01.dbf
1 row selected.
Elapsed: 00:00:00.02
18:00:05 SYS @ sink >select tablespace_name,contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY
USERS PERMANENT
EXAMPLE PERMANENT
TBSSSS PERMANENT
7 rows selected.
Elapsed: 00:00:00.01
把临时表空间temp里面的临时文件temp01.dbf加入到ASM中的+date目录下
18:00:24 SYS @ sink >alter tablespace temp add tempfile '+data';
Tablespace altered.
Elapsed: 00:00:00.11
18:01:27 SYS @ sink >select name from v$tempfile;
NAME
-------------------------------------------------------
/u01/app/oracle/oradata/sink/temp01.dbf
+DATA/sink/tempfile/temp.268.964720887
2 rows selected.
Elapsed: 00:00:00.01
因为只有一个临时表空间的一个临时文件所以无法删除，多个就能成功，一直到最后一个就不能这样删除了
18:12:22 SYS @ sink >alter tablespace temp drop tempfile '/u01/app/oracle/oradata/sink/temp01.dbf';
Tablespace altered.
Elapsed: 00:00:00.12
18:13:35 SYS @ sink >select name from v$tempfile;
NAME
-------------------------------------------------------
/u01/app/oracle/oradata/sink/temp01.dbf
+DATA/sink/tempfile/temp.268.964720887
2 rows selected.
Elapsed: 00:00:00.01
上一步中DB处于open，以上操作将临时文件删至1个的时候，就不能继续删除，得将DB置于mount状态才行
18:14:03 SYS @ sink >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
18:19:51 SYS @ sink >startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 553651280 bytes
Database Buffers 276824064 bytes
Redo Buffers 2371584 bytes
Database mounted.
18:20:08 SYS @ sink >alter database drop tempfile '/u01/app/oracle/oradata/sink/temp01.dbf';
alter database drop tempfile '/u01/app/oracle/oradata/sink/temp01.dbf'
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected
Elapsed: 00:00:00.00
18:21:45 SYS @ sink >edit
Wrote file afiedt.buf
1* alter database tempfile '/u01/app/oracle/oradata/sink/temp01.dbf' drop
18:22:16 SYS @ sink >r
1* alter database tempfile '/u01/app/oracle/oradata/sink/temp01.dbf' drop
Database altered.
Elapsed: 00:00:00.03
18:22:17 SYS @ sink >alter database open;
Database altered.
Elapsed: 00:00:02.34
18:23:19 SYS @ sink >col tablespace_name for a10
18:23:38 SYS @ sink >col file_name for a55
18:23:54 SYS @ sink >r
1* select tablespace_name,file_name from dba_temp_files
TABLESPACE FILE_NAME
---------- -------------------------------------------------------
TEMP +DATA/sink/tempfile/temp.268.964720887
1 row selected.
Elapsed: 00:00:00.00
迁移日志文件到ASM，具体细节就不多做解释了，整了这么久，累，去吃饭去了
18:23:55 SYS @ sink >select group#,status,sequence#,bytes from v$log;
GROUP# STATUS SEQUENCE# BYTES
---------- ---------------- ---------- ----------
1 INACTIVE 16 52428800
2 CURRENT 17 52428800
3 INACTIVE 15 52428800
3 rows selected.
Elapsed: 00:00:00.01
18:29:19 SYS @ sink >col member for a55
18:29:37 SYS @ sink >select group#,member from v$logfile;
GROUP# MEMBER
---------- -------------------------------------------------------
3 /u01/app/oracle/oradata/sink/redo03.log
2 /u01/app/oracle/oradata/sink/redo02.log
1 /u01/app/oracle/oradata/sink/redo01.log
3 rows selected.
Elapsed: 00:00:00.01
18:29:57 SYS @ sink >alter database add logfile '+data' size 50m;
Database altered.
Elapsed: 00:00:01.41
18:30:32 SYS @ sink >alter database add logfile '+data' size 50m;
Database altered.
Elapsed: 00:00:00.62
18:30:54 SYS @ sink >select group#,member from v$logfile;
GROUP# MEMBER
---------- -------------------------------------------------------
3 /u01/app/oracle/oradata/sink/redo03.log
2 /u01/app/oracle/oradata/sink/redo02.log
1 /u01/app/oracle/oradata/sink/redo01.log
4 +DATA/sink/onlinelog/group_4.269.964722631
5 +DATA/sink/onlinelog/group_5.270.964722653
5 rows selected.
Elapsed: 00:00:00.00
18:31:13 SYS @ sink >select group#,status,sequence#,bytes from v$log;
GROUP# STATUS SEQUENCE# BYTES
---------- ---------------- ---------- ----------
1 INACTIVE 16 52428800
2 CURRENT 17 52428800
3 INACTIVE 15 52428800
4 UNUSED 0 52428800
5 UNUSED 0 52428800
5 rows selected.
Elapsed: 00:00:00.02
18:33:37 SYS @ sink >alter database drop logfile group 1;
Database altered.
Elapsed: 00:00:00.01
18:35:03 SYS @ sink >alter database drop logfile group 3;
Database altered.
Elapsed: 00:00:00.01
18:35:20 SYS @ sink >select group#,status,sequence#,bytes from v$log;
GROUP# STATUS SEQUENCE# BYTES
---------- ---------------- ---------- ----------
2 CURRENT 17 52428800
4 UNUSED 0 52428800
5 UNUSED 0 52428800
3 rows selected.
Elapsed: 00:00:00.00
18:35:56 SYS @ sink >alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
18:36:31 SYS @ sink >r
1* alter system switch logfile
System altered.
Elapsed: 00:00:00.01
18:36:37 SYS @ sink >select group#,status,sequence#,bytes from v$log;
GROUP# STATUS SEQUENCE# BYTES
---------- ---------------- ---------- ----------
2 ACTIVE 17 52428800
4 ACTIVE 18 52428800
5 CURRENT 19 52428800
3 rows selected.
Elapsed: 00:00:00.01
18:37:00 SYS @ sink >alter system checkpoint;
System altered.
Elapsed: 00:00:00.01
18:37:35 SYS @ sink >select group#,status,sequence#,bytes from v$log;
GROUP# STATUS SEQUENCE# BYTES
---------- ---------------- ---------- ----------
2 INACTIVE 17 52428800
4 INACTIVE 18 52428800
5 CURRENT 19 52428800
3 rows selected.
Elapsed: 00:00:00.01
18:38:02 SYS @ sink >alter database drop logfile group 2;
Database altered.
Elapsed: 00:00:00.02
18:38:54 SYS @ sink >select group#,status,sequence#,bytes from v$log;
GROUP# STATUS SEQUENCE# BYTES
---------- ---------------- ---------- ----------
4 INACTIVE 18 52428800
5 CURRENT 19 52428800
2 rows selected.
Elapsed: 00:00:00.00
18:39:30 SYS @ sink >select group#,member from v$logfile;
GROUP# MEMBER
---------- -------------------------------------------------------
4 +DATA/sink/onlinelog/group_4.269.964722631
5 +DATA/sink/onlinelog/group_5.270.964722653
2 rows selected.
Elapsed: 00:00:00.00
18:40:00 SYS @ sink >alter database add logfile '+data' size 50m;
Database altered.
Elapsed: 00:00:00.32
18:40:36 SYS @ sink >select group#,member from v$logfile;
GROUP# MEMBER
---------- -------------------------------------------------------
1 +DATA/sink/onlinelog/group_1.271.964723237
4 +DATA/sink/onlinelog/group_4.269.964722631
5 +DATA/sink/onlinelog/group_5.270.964722653
3 rows selected.
Elapsed: 00:00:00.00
好了，所有成果的效果，数据迁移完成（参数文件，控制文件，数据文件，日志文件）
18:41:47 SYS @ sink >select name from v$datafile
18:41:59 2 union all
18:42:03 3 select name from v$controlfile
18:42:18 4 union all
18:42:21 5 select member name from v$logfile;
NAME
-------------------------------------------------------
+DATA/sink/datafile/system.258.964712643
+DATA/sink/datafile/sysaux.257.964712643
+DATA/sink/datafile/undotbs1.261.964712645
+DATA/sink/datafile/users.263.964712657
+DATA/sink/datafile/example.260.964712643
+DATA/sink/datafile/tbssss.259.964712643
+DATA/sink/controlfile/current.266.964717197
+DATA/sink/controlfile/current.267.964717197
+DATA/sink/onlinelog/group_1.271.964723237
+DATA/sink/onlinelog/group_4.269.964722631
+DATA/sink/onlinelog/group_5.270.964722653
11 rows selected.
Elapsed: 00:00:00.01
18:42:35 SYS @ sink >
这里就是迁移到ASM的效果了
ASMCMD> pwd
+data/sink/datafile
ASMCMD> ls
EXAMPLE.260.964712643
SYSAUX.257.964712643
SYSTEM.258.964712643
TBSSSS.256.963504823
TBSSSS.259.964712643
UNDOTBS1.261.964712645
USERS.263.964712657
ASMCMD> cd ../controlfile
ASMCMD> pwd
+data/sink/controlfile
ASMCMD> ls
Backup.262.964712653
current.266.964717197
current.267.964717197
ASMCMD> cd ../onlinelog
ASMCMD> pwd
+data/sink/onlinelog
ASMCMD> ls
group_1.271.964723237
group_4.269.964722631
group_5.270.964722653
ASMCMD>
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注恒创行业资讯频道，感谢您对恒创的支持。
.png)
.png)
