大鲨鱼o0O

保持理智,冷静分析,懂得择决,默默前行

深厚的内功远远大于精妙的招式


ASM文件迁移

尊重原创,转载请注明出处,谢谢

打开数据库

15:35:11 SYS @ sink >startup
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.
15:35:25 SYS @ sink >

exit退出SQL环境,进入linux操作系统环境,此时的oracle还处于open状态

15:35:25 SYS @ sink >exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@sink ~]$
在linux的oracle用户下进入rman环境。  rman target /   等价于   rman target sys/oracle@sink
[oracle@sink ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jan 6 15:41:27 2018

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

connected to target database: SINK (DBID=207714324)

RMAN>

在做一系列的没把握的未知操作之前先做一个 0 级增量备份 最保险

RMAN> run
2> {
3> allocate channel dev1 device type disk;
4> allocate channel dev2 device type disk;
5> allocate channel dev3 device type disk;
6> allocate channel dev4 device type disk;
7> backup as copy incremental level 0 database format '+DATA' tag 'ora_asm_migration';
8> }

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

allocated channel: dev2
channel dev2: SID=41 device type=DISK

allocated channel: dev3
channel dev3: SID=40 device type=DISK

allocated channel: dev4
channel dev4: SID=42 device type=DISK

Starting backup at 06-JAN-18
channel dev1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/sink/system01.dbf
channel dev2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/sink/sysaux01.dbf
channel dev3: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/sink/example01.dbf
channel dev4: starting datafile copy
input datafile file number=00006 name=+DATA/sink/datafile/tbssss.256.963504823
output file name=+DATA/sink/datafile/tbssss.259.964712643 tag=ORA_ASM_MIGRATION RECID=3 STAMP=964712644
channel dev4: datafile copy complete, elapsed time: 00:00:03
channel dev4: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/sink/undotbs01.dbf
output file name=+DATA/sink/datafile/undotbs1.261.964712645 tag=ORA_ASM_MIGRATION RECID=4 STAMP=964712652
channel dev4: datafile copy complete, elapsed time: 00:00:07
channel dev4: starting datafile copy
copying current control file
output file name=+DATA/sink/controlfile/backup.262.964712653 tag=ORA_ASM_MIGRATION RECID=5 STAMP=964712654
channel dev4: datafile copy complete, elapsed time: 00:00:03
channel dev4: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/sink/users01.dbf
output file name=+DATA/sink/datafile/users.263.964712657 tag=ORA_ASM_MIGRATION RECID=6 STAMP=964712656
channel dev4: datafile copy complete, elapsed time: 00:00:01
channel dev4: starting incremental level 0 datafile backup set
channel dev4: specifying datafile(s) in backup set
including current SPFILE in backup set
channel dev4: starting piece 1 at 06-JAN-18
channel dev4: finished piece 1 at 06-JAN-18
piece handle=+DATA/sink/backupset/2018_01_06/nnsnn0_ora_asm_migration_0.264.964712657 tag=ORA_ASM_MIGRATION comment=NONE
channel dev4: backup set complete, elapsed time: 00:00:01
output file name=+DATA/sink/datafile/example.260.964712643 tag=ORA_ASM_MIGRATION RECID=7 STAMP=964712658
channel dev3: datafile copy complete, elapsed time: 00:00:18
output file name=+DATA/sink/datafile/sysaux.257.964712643 tag=ORA_ASM_MIGRATION RECID=8 STAMP=964712662
channel dev2: datafile copy complete, elapsed time: 00:00:22
output file name=+DATA/sink/datafile/system.258.964712643 tag=ORA_ASM_MIGRATION RECID=9 STAMP=964712665
channel dev1: datafile copy complete, elapsed time: 00:00:30
Finished backup at 06-JAN-18
released channel: dev1
released channel: dev2
released channel: dev3
released channel: dev4

RMAN>

数据库全备 仅仅是一个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>

转载请注明:sinkshark的博客

打赏一个呗

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

....