遷移資料庫檔案到ASM
透過將資料庫備份一份映象到ASM,以及spfile，controlfile,logfile,tempfile的遷移過程。環境是11.2.3.0，節點已安裝了grid，配置了ASM例項
[oracle@hp580-5 oracle]$ pwd
/s01/app/oracle
[oracle@hp580-5 oracle]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 17 11:24:28 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /s01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileoral.ora
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@hp580-5 oracle]$ cp /s01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileoral.ora bkspfileoral.ora
[oracle@hp580-5 oracle]$ rman
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Sep 17 11:29:20 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: ORAL (DBID=1219538183)
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
8> INCREMENTAL LEVEL 0
9> DATABASE
10> FORMAT '+DATA'
11> TAG 'ORA_ASM_MIGRATION';
12> }
using target database control file instead of recovery catalog
allocated channel: dev1
channel dev1: SID=45 device type=DISK
allocated channel: dev2
channel dev2: SID=50 device type=DISK
allocated channel: dev3
channel dev3: SID=56 device type=DISK
allocated channel: dev4
channel dev4: SID=62 device type=DISK
Starting backup at 17-SEP-15
channel dev1: starting datafile copy
input datafile file number=00006 name=/s01/app/oracle/oradata/oral/eport01.dbf
channel dev2: starting datafile copy
input datafile file number=00001 name=/s01/app/oracle/oradata/oral/system01.dbf
channel dev3: starting datafile copy
input datafile file number=00002 name=/s01/app/oracle/oradata/oral/sysaux01.dbf
channel dev4: starting datafile copy
input datafile file number=00005 name=/s01/app/oracle/oradata/oral/example01.dbf
output file name=+DATA/oral/datafile/example.285.890652577 tag=ORA_ASM_MIGRATION RECID=2 STAMP=890652599
channel dev4: datafile copy complete, elapsed time: 00:00:35
channel dev4: starting datafile copy
input datafile file number=00003 name=/s01/app/oracle/oradata/oral/undotbs01.dbf
output file name=+DATA/oral/datafile/system.287.890652577 tag=ORA_ASM_MIGRATION RECID=5 STAMP=890652620
channel dev2: datafile copy complete, elapsed time: 00:00:50
channel dev2: starting datafile copy
copying current control file
output file name=+DATA/oral/datafile/sysaux.286.890652577 tag=ORA_ASM_MIGRATION RECID=3 STAMP=890652615
channel dev3: datafile copy complete, elapsed time: 00:00:50
channel dev3: starting datafile copy
input datafile file number=00004 name=/s01/app/oracle/oradata/oral/users01.dbf
output file name=+DATA/oral/datafile/undotbs1.289.890652609 tag=ORA_ASM_MIGRATION RECID=4 STAMP=890652619
channel dev4: datafile copy complete, elapsed time: 00:00:17
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 17-SEP-15
output file name=+DATA/oral/datafile/tbs_eport.288.890652577 tag=ORA_ASM_MIGRATION RECID=8 STAMP=890652627
channel dev1: datafile copy complete, elapsed time: 00:00:55
output file name=+DATA/oral/controlfile/backup.290.890652623 tag=ORA_ASM_MIGRATION RECID=7 STAMP=890652627
channel dev2: datafile copy complete, elapsed time: 00:00:05
output file name=+DATA/oral/datafile/users.291.890652625 tag=ORA_ASM_MIGRATION RECID=6 STAMP=890652627
channel dev3: datafile copy complete, elapsed time: 00:00:03
channel dev4: finished piece 1 at 17-SEP-15
piece handle=+DATA/oral/backupset/2015_09_17/nnsnn0_ora_asm_migration_0.292.890652627 tag=ORA_ASM_MIGRATION comment=NONE
channel dev4: backup set complete, elapsed time: 00:00:03
Finished backup at 17-SEP-15
released channel: dev1
released channel: dev2
released channel: dev3
released channel: dev4
RMAN> SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT
RMAN> BACKUP AS BACKUPSET SPFILE;
Starting backup at 17-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-SEP-15
channel ORA_DISK_1: finished piece 1 at 17-SEP-15
piece handle=/opt/oracle/oradata/fra/ORAL/backupset/2015_09_17/o1_mf_nnsnf_TAG20150917T113301_bznf3g1v_.bkp tag=TAG20150917T113301 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-SEP-15
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN>
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 9754144768 bytes
Fixed Size 2236648 bytes
Variable Size 1577062168 bytes
Database Buffers 8153726976 bytes
Redo Buffers 21118976 bytes
RMAN> restore spfile to '+DATA/spfileoral.ora';
Starting restore at 17-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=193 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/spfileoral.ora
channel ORA_DISK_1: reading from backup piece /opt/oracle/oradata/fra/ORAL/backupset/2015_09_17/o1_mf_nnsnf_TAG20150917T113301_bznf3g1v_.bkp
channel ORA_DISK_1: piece handle=/opt/oracle/oradata/fra/ORAL/backupset/2015_09_17/o1_mf_nnsnf_TAG20150917T113301_bznf3g1v_.bkp tag=TAG20150917T113301
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-SEP-15
SQL> connect / as sysdba
Connected.
SQL> startup force nomount;
ORACLE instance started.
Total System Global Area 9754144768 bytes
Fixed Size 2236648 bytes
Variable Size 1577062168 bytes
Database Buffers 8153726976 bytes
Redo Buffers 21118976 bytes
SQL> alter system set db_create_file_dest='+DATA';
System altered.
SQL> alter system set db_recovery_file_dest_size=100g;
System altered.
SQL> alter system set db_recovery_file_dest='+FRA';
System altered.
SQL> startup force nomount;
ORACLE instance started.
Total System Global Area 9754144768 bytes
Fixed Size 2236648 bytes
Variable Size 1577062168 bytes
Database Buffers 8153726976 bytes
Redo Buffers 21118976 bytes
SQL> alter system set control_files='+DATA','+FRA' SCOPE=SPFILE;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@hp580-5 oracle]$ rman
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Sep 17 13:39:32 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: ORAL (not mounted)
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 9754144768 bytes
Fixed Size 2236648 bytes
Variable Size 1577062168 bytes
Database Buffers 8153726976 bytes
Redo Buffers 21118976 bytes
RMAN> restore controlfile from '/s01/app/oracle/oradata/oral/control01.ctl';
Starting restore at 17-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=223 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/oral/controlfile/current.294.890660453
output file name=+FRA/oral/controlfile/current.1357.890660453
Finished restore at 17-SEP-15
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> SWITCH DATABASE TO COPY;
RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
RECOVER DATABASE;
}datafile 1 switched to datafile copy "+DATA/oral/datafile/system.287.890652577"
datafile 2 switched to datafile copy "+DATA/oral/datafile/sysaux.286.890652577"
datafile 3 switched to datafile copy "+DATA/oral/datafile/undotbs1.289.890652609"
datafile 4 switched to datafile copy "+DATA/oral/datafile/users.291.890652625"
datafile 5 switched to datafile copy "+DATA/oral/datafile/example.285.890652577"
datafile 6 switched to datafile copy "+DATA/oral/datafile/tbs_eport.288.890652577"
RMAN> 2> 3> 4> 5> 6> 7> 8>
allocated channel: dev1
channel dev1: SID=223 device type=DISK
allocated channel: dev2
channel dev2: SID=235 device type=DISK
allocated channel: dev3
channel dev3: SID=241 device type=DISK
allocated channel: dev4
channel dev4: SID=247 device type=DISK
Starting recover at 17-SEP-15
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-SEP-15
released channel: dev1
released channel: dev2
released channel: dev3
released channel: dev4
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@hp580-5 oracle]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 17 13:50:06 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> alter database tempfile '/s01/app/oracle/oradata/oral/temp01.dbf' drop;
Database altered.
SQL> alter tablespace temp add tempfile;
Tablespace altered.
SQL> select group#,bytes
2 from v$log;
GROUP# BYTES
---------- ----------
1 52428800
2 52428800
3 52428800
SQL> alter database add logfile size 100m;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database add logfile size 100m;
Database altered.
SQL> alter database add logfile size 100m;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 9754144768 bytes
Fixed Size 2236648 bytes
Variable Size 1577062168 bytes
Database Buffers 8153726976 bytes
Redo Buffers 21118976 bytes
Database mounted.
Database opened.
SQL>
SQL> select member ,group# from v$logfile;
MEMBER
--------------------------------------------------------------------------------
GROUP#
----------
+DATA/oral/onlinelog/group_2.297.890661591
2
+FRA/oral/onlinelog/group_2.1345.890661593
2
+DATA/oral/onlinelog/group_1.299.890661759
1
MEMBER
--------------------------------------------------------------------------------
GROUP#
----------
+DATA/oral/onlinelog/group_3.298.890661597
3
+FRA/oral/onlinelog/group_3.1011.890661599
3
+FRA/oral/onlinelog/group_1.532.890661761
1
6 rows selected.
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIM TS# RFILE# STATUS
---------- ---------------- ------------ ---------- ---------- -------
ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
1 1100738 17-SEP-15 3 1 ONLINE
READ WRITE 104857600 12800 104857600 8192
+DATA/oral/tempfile/temp.295.890661063
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26870952/viewspace-1802168/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【資料遷移】RMAN遷移資料庫到ASM(二)切換資料檔案到ASM資料庫ASM
- 使用RMAN遷移檔案系統資料庫到ASM資料庫ASM
- 遷移資料庫到ASM資料庫ASM
- 如何遷移ASM資料檔案到檔案系統ASM
- 用rman從檔案系統遷移資料庫到asm資料庫ASM
- oracle 遷移資料庫到asmOracle資料庫ASM
- 【資料遷移】RMAN遷移資料庫到ASM(一)建立ASM磁碟組資料庫ASM
- 四、用rman從檔案系統遷移資料庫到asm資料庫ASM
- 【資料遷移】RMAN遷移資料庫到ASM(三)遷移onlinelog等到ASM資料庫ASM
- 用RMAN遷移檔案到ASM或從ASM遷出ASM
- 遷移已存在的資料庫到ASM中資料庫ASM
- 資料庫檔案的遷移資料庫
- 遷移資料庫的檔案到不同路徑(轉)資料庫
- 移動資料檔案從ASM到檔案系統ASM
- 利用RMAN在檔案系統與ASM之間遷移資料庫ASM資料庫
- 資料庫從檔案系統遷移到ASM資料庫ASM
- ASM檔案系統遷移ASM
- ASM下遷移控制檔案ASM
- asm 檔案系統遷移ASM
- 11g資料庫遷移ASM資料庫ASM
- 資料檔案遷移
- 將資料庫從ASM遷移到檔案系統資料庫ASM
- 將資料庫從檔案系統遷移到ASM資料庫ASM
- ASM的資料庫遷移回到DISK上ASM資料庫
- 遷移檔案系統管理下的db到asm下ASM
- 達夢資料庫資料檔案遷移過程資料庫
- dataguard備庫的資料檔案的遷移
- 三、rman 資料庫遷移--從檔案系統到裸裝置資料庫
- 遷移資料庫到SQLonLinuxDocker資料庫SQLLinuxDocker
- 資料檔案的遷移
- oracle 資料檔案遷移Oracle
- oracle資料檔案遷移Oracle
- 移動資料檔案從檔案系統到ASM磁碟組中ASM
- 利用rman遷移裸裝置資料檔案到檔案系統
- 利用RMAN將資料庫從檔案系統遷移到ASM資料庫ASM
- Oracle_遷移資料檔案Oracle
- 資料庫遷移到ASM資料庫ASM
- 【ASM學習】普通資料庫向ASM例項的遷移(二)ASM資料庫