共计 16803 个字符，预计需要花费 43 分钟才能阅读完成。
文件系统怎样迁移到 ASM，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面丸趣 TV 小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
数据库全备
仅仅是一个 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 nomount
alter 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
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注丸趣 TV 行业资讯频道，感谢您对丸趣 TV 的支持。
