使用RMAN將RAC+ASM複製到單例項+ASM上
本次試驗將部署到ASM上的雙節點RAC複製到單例項的ASM上。
該試驗也是出自於一個專案的需求，在虛擬機器上演練操作特此記錄。
之前已經做過多次RMAN複製資料庫，這次試驗的特點是從RAC複製到單例項。需要注意的以下內容：
1，spfile引數問題
RAC下的spfile引數中記錄了很多和叢集相關的資訊，在複製時需要修改。比如
*.cluster_database=true
PROD2.instance_number=2
PROD1.instance_number=1
*.remote_listener='cluster-scan:1521'
PROD2.thread=2
PROD1.thread=1
PROD2.undo_tablespace='UNDOTBS2'
PROD1.undo_tablespace=‘UNDOTBS1’
和RAC相關的資訊可以選擇註釋掉，或者刪除。
2，undo表空間問題
雙節點RAC的備份集中會產生2個undo表空間及其資料檔案，而單例項資料庫只需要一個undo表空間。使用RAC的備份集在單例項資料庫上恢復後，可以手工刪除多餘的那個undo表空間及其資料檔案，通常是undotbs2
3，redo問題
雙節點RAC的控制檔案的備份中記錄的是兩個例項的redo資訊，恢復完成後在open resetlogs開啟資料庫後回建立出thread2的online redo log，也就是節點2的聯機日誌。同undotbs2的處理原則一樣，刪除之。
1，RAC 端rman全備資料庫到/backup目錄下。
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 29 03:15:49 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=271163854)
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> backup tag 'full' format '/backup/full_%U.bak'
5> database include current controlfile;
6> sql 'alter system archive log current';
7> backup tag 'arch' format '/backup/arch_%U.arc'
8> archivelog all;
9> release channel c1;
10> release channel c2;
11> }
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
43 Full 522.99M DISK 00:00:04 2014-09-29 03:05:29
BP Key: 43 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /backup/full_1bpjmdbl_1_1.bak
List of Datafiles in backup set 43
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
2 Full 1298717 2014-09-29 03:05:25 +DATA/prod/datafile/sysaux.257.859325451
4 Full 1298717 2014-09-29 03:05:25 +DATA/prod/datafile/users.259.859325451
5 Full 1298717 2014-09-29 03:05:25 +DATA/prod/datafile/example.264.859325525
6 Full 1298717 2014-09-29 03:05:25 +DATA/prod/datafile/undotbs2.265.859325695
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
44 Full 649.16M DISK 00:00:04 2014-09-29 03:05:29
BP Key: 44 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /backup/full_1cpjmdbl_1_1.bak
List of Datafiles in backup set 44
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1298719 2014-09-29 03:05:25 +DATA/prod/datafile/system.256.859325451
3 Full 1298719 2014-09-29 03:05:25 +DATA/prod/datafile/undotbs1.258.859325451
7 Full 1298719 2014-09-29 03:05:25 +DATA/prod/datafile/goldengate.269.859338811
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
45 Full 80.00K DISK 00:00:00 2014-09-29 03:05:32
BP Key: 45 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /backup/full_1epjmdbs_1_1.bak << 引數檔案所在備份片，後面恢復時候需要用到
SPFILE Included: Modification time: 2014-09-29 02:02:18
SPFILE db_unique_name: PROD
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
46 Full 17.80M DISK 00:00:01 2014-09-29 03:05:33
BP Key: 46 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /backup/full_1dpjmdbs_1_1.bak << 控制檔案所在備份片，後面恢復時候需要用到
Control File Included: Ckp SCN: 1298731 Ckp time: 2014-09-29 03:05:32
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
47 53.63M DISK 00:00:00 2014-09-29 03:05:44
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: ARCH
Piece Name: /backup/arch_1gpjmdc8_1_1.arc
List of Archived Logs in backup set 47
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 28 1260638 2014-09-28 23:53:08 1260655 2014-09-28 23:53:11
1 29 1260655 2014-09-28 23:53:11 1298750 2014-09-29 03:05:34
1 30 1298750 2014-09-29 03:05:34 1298772 2014-09-29 03:05:40
2 24 1260645 2014-09-28 23:53:19 1260660 2014-09-28 23:53:22
2 25 1260660 2014-09-28 23:53:22 1298755 2014-09-29 03:05:42
2 26 1298755 2014-09-29 03:05:42 1298767 2014-09-29 03:05:45
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
48 62.20M DISK 00:00:00 2014-09-29 03:05:44
BP Key: 48 Status: AVAILABLE Compressed: NO Tag: ARCH
Piece Name: /backup/arch_1fpjmdc8_1_1.arc
List of Archived Logs in backup set 48
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 25 1211968 2014-09-28 19:28:25 1221060 2014-09-28 20:26:23
1 26 1221060 2014-09-28 20:26:23 1221084 2014-09-28 20:26:29
1 27 1221084 2014-09-28 20:26:29 1260638 2014-09-28 23:53:08
2 21 1211964 2014-09-28 19:28:23 1221067 2014-09-28 20:26:25
2 22 1221067 2014-09-28 20:26:25 1221089 2014-09-28 20:26:31
2 23 1221089 2014-09-28 20:26:31 1260645 2014-09-28 23:53:19
2，RAC端使用scp傳輸備份集到單例項端/home/oracle目錄下
[oracle@node1 ~]$ scp /backup/* oracle@172.16.228.8:/home/oracle
單例項端檢視接收到的備份集
[oracle@single ~]$ ls -l /home/oracle
total 1338624
-rw-r----- 1 oracle oinstall 65219584 Oct 8 21:39 arch_1fpjmdc8_1_1.arc
-rw-r----- 1 oracle oinstall 56237568 Oct 8 21:39 arch_1gpjmdc8_1_1.arc
-rw-r----- 1 oracle oinstall 548405248 Oct 8 21:39 full_1bpjmdbl_1_1.bak
-rw-r----- 1 oracle oinstall 680697856 Oct 8 21:40 full_1cpjmdbl_1_1.bak
-rw-r----- 1 oracle oinstall 18677760 Oct 8 21:40 full_1dpjmdbs_1_1.bak
-rw-r----- 1 oracle oinstall 98304 Oct 8 21:40 full_1epjmdbs_1_1.bak
3，單例項端使用RMAN從備份集中恢復pfile到/home/oracle/racpfile.ora
$ rman target /
RMAN> startup nomount;
RMAN> restore spfile to pfile '/home/oracle/racpfile.ora' from '/home/oracle/full_1epjmdbs_1_1.bak';
4，單例項端修改pfile檔案，將叢集相關的資訊全部註釋掉
[oracle@single ~]$ cat racpfile.ora
##PROD1.__db_cache_size=146800640
##PROD2.__db_cache_size=176160768
##PROD1.__java_pool_size=4194304
##PROD2.__java_pool_size=4194304
##PROD1.__large_pool_size=8388608
##PROD2.__large_pool_size=8388608
##PROD1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
##PROD1.__pga_aggregate_target=289406976
##PROD2.__pga_aggregate_target=293601280
##PROD1.__sga_target=385875968
##PROD2.__sga_target=381681664
##PROD1.__shared_io_pool_size=0
##PROD2.__shared_io_pool_size=0
##PROD1.__shared_pool_size=218103808
##PROD2.__shared_pool_size=184549376
##PROD1.__streams_pool_size=0
##PROD2.__streams_pool_size=0
##*.audit_file_dest='/u01/app/oracle/admin/PROD/adump'
*.audit_file_dest='/u01/admin/PROD/adump'
*.audit_trail='db'
##*.cluster_database=true << 叢集
*.compatible='11.2.0.4.0'
##*.control_files='+DATA/prod/controlfile/current.260.859325519','+ARCH/prod/controlfile/current.256.859325519'
*.control_files='+DATA/PROD/controlfile/control01.ctl','+FRA/PROD/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='PROD'
##*.db_recovery_file_dest='+ARCH'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4621074432
##*.diagnostic_dest='/u01/app/oracle'
*.diagnostic_dest='/u01/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
*.enable_goldengate_replication=TRUE
##PROD2.instance_number=2
##PROD1.instance_number=1
##*.memory_target=675282944
*.memory_target=583008256
*.open_cursors=300
*.processes=150
##*.remote_listener='cluster-scan:1521' << scan IP
*.remote_login_passwordfile='exclusive'
##PROD2.thread=2
##PROD1.thread=1
##PROD2.undo_tablespace='UNDOTBS2'
##PROD1.undo_tablespace='UNDOTBS1'
*.undo_tablespace='UNDOTBS1' << 此處只保留unodtbs1的引數，在後面的恢復中還是會將undotbs2給恢復出來。
5，單例項端SQLPLUS使用修改後的pfile檔案啟動例項到nomount狀態
SYS@PROD >startup nomount pfile=/home/oracle/racpfile.ora;
6，單例項端建立spfile到ASM磁碟組中的+DATA/PROD
SYS@PROD >create spfile='+DATA/PROD/spfilePROD.ora' from memory;
7，單例項端建立pfile，指引spfile的檔案位置
$ vi $ORACLE_HOME/dbs/initPROD.ora
spfile='+DATA/PROD/spfilePROD.ora'
8，單例項端使用RMAN啟動到nomount狀態
RMAN> startup force nomount;
RMAN> set DBID=271163854
9，單例項端使用RAMN從備份集中恢復控制檔案
RMAN> restore controlfile from '/home/oracle/full_1dpjmdbs_1_1.bak’;
10，單例項端mount資料庫
RMAN> mount database;
11，單例項端註冊備份集的路徑
RMAN> catalog start with '/home/oracle';
12，單例項端列出控制檔案中記錄的資料檔案
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name PROD
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** +DATA/prod/datafile/system.256.859325451
2 0 SYSAUX *** +DATA/prod/datafile/sysaux.257.859325451
3 0 UNDOTBS1 *** +DATA/prod/datafile/undotbs1.258.859325451
4 0 USERS *** +DATA/prod/datafile/users.259.859325451
5 0 EXAMPLE *** +DATA/prod/datafile/example.264.859325525
6 0 UNDOTBS2 *** +DATA/prod/datafile/undotbs2.265.859325695
7 0 GOLDENGATE *** +DATA/prod/datafile/goldengate.269.859338811
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/prod/tempfile/temp.263.859325523
13，單例項端根據列出的資料檔案採用set newname的方式來修改檔案路徑。
RMAN> run{
2> set newname for datafile 1 to '+DATA';
3> set newname for datafile 2 to '+DATA';
4> set newname for datafile 3 to '+DATA';
5> set newname for datafile 4 to '+DATA';
6> set newname for datafile 5 to '+DATA';
7> set newname for datafile 6 to '+DATA';
8> set newname for datafile 7 to '+DATA';
9> set newname for tempfile 1 to '+DATA’; <
10> restore database;
11> switch datafile all;
12> switch tempfile all;
13> recover database;
14> }
14，單例項端resetlogs方式開啟資料庫庫
RMAN> alter database open resetlogs;
15，單例項端檢視redo log的分佈情況，注意thread=2是RAC中第二個節點上的，單例項中不需要刻意刪除掉。
SYS@PROD >SELECT v$logfile.member, v$logfile.group#,thread#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024/1024 ,v$log.thread#
2 FROM v$log, v$logfile
3 WHERE v$log.group# = v$logfile.group#
4 ORDER BY v$log.thread#,v$logfile.group#;
MEMBER GROUP# THREAD# STATUS ARC V$LOG.BYTES/1024/1024 THREAD#
-------------------------------------------------- ---------- ---------- ---------------- --- --------------------- ----------
+FRA/prod/onlinelog/group_1.308.860453423 1 1 CURRENT NO 50 1
+DATA/prod/onlinelog/group_1.286.860453423 1 1 CURRENT NO 50 1
+FRA/prod/onlinelog/group_2.261.860453423 2 1 UNUSED YES 50 1
+DATA/prod/onlinelog/group_2.263.860453423 2 1 UNUSED YES 50 1
+DATA/prod/onlinelog/group_3.295.860453423 3 2 INACTIVE YES 50 2
+FRA/prod/onlinelog/group_3.260.860453423 3 2 INACTIVE YES 50 2
+DATA/prod/onlinelog/group_4.258.860453423 4 2 UNUSED YES 50 2
+FRA/prod/onlinelog/group_4.307.860453423 4 2 UNUSED YES 50 2
16，禁用thread 2
SYS@PROD >alter database disable thread 2;
17，刪除thread=2的日誌組group3和group4
SYS@PROD >alter database drop logfile group 3;
SYS@PROD >alter database drop logfile group 4;
刪除thread=2的日誌組後的redo log分佈
SYS@PROD >SELECT v$logfile.member, v$logfile.group#,thread#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024/1024 ,v$log.thread#
2 FROM v$log, v$logfile
3 WHERE v$log.group# = v$logfile.group#
4 ORDER BY v$log.thread#,v$logfile.group#;
MEMBER GROUP# THREAD# STATUS ARC V$LOG.BYTES/1024/1024 THREAD#
-------------------------------------------------- ---------- ---------- ---------------- --- --------------------- ----------
+DATA/prod/onlinelog/group_1.286.860453423 1 1 CURRENT NO 50 1
+FRA/prod/onlinelog/group_1.308.860453423 1 1 CURRENT NO 50 1
+DATA/prod/onlinelog/group_2.263.860453423 2 1 UNUSED YES 50 1
+FRA/prod/onlinelog/group_2.261.860453423 2 1 UNUSED YES 50 1
18，同樣思路，刪除不屬於單例項的undotbs2表空間及其資料檔案
SYS@PROD >col name for a50
SYS@PROD >select name from v$datafile;
NAME
--------------------------------------------------
+DATA/prod/datafile/system.296.860453223
+DATA/prod/datafile/sysaux.262.860453229
+DATA/prod/datafile/undotbs1.273.860453223
+DATA/prod/datafile/users.261.860453229
+DATA/prod/datafile/example.293.860453229
+DATA/prod/datafile/undotbs2.285.860453229 << 多餘的undotbs2
+DATA/prod/datafile/goldengate.259.860453223
7 rows selected.
SYS@PROD >select name from v$tablespace where name like 'UNDO%';
NAME
--------------------------------------------------
UNDOTBS1
UNDOTBS2
SYS@PROD >drop tablespace undotbs2 including contents and datafiles;
此次試驗結束
該試驗也是出自於一個專案的需求，在虛擬機器上演練操作特此記錄。
之前已經做過多次RMAN複製資料庫，這次試驗的特點是從RAC複製到單例項。需要注意的以下內容：
1，spfile引數問題
RAC下的spfile引數中記錄了很多和叢集相關的資訊，在複製時需要修改。比如
*.cluster_database=true
PROD2.instance_number=2
PROD1.instance_number=1
*.remote_listener='cluster-scan:1521'
PROD2.thread=2
PROD1.thread=1
PROD2.undo_tablespace='UNDOTBS2'
PROD1.undo_tablespace=‘UNDOTBS1’
和RAC相關的資訊可以選擇註釋掉，或者刪除。
2，undo表空間問題
雙節點RAC的備份集中會產生2個undo表空間及其資料檔案，而單例項資料庫只需要一個undo表空間。使用RAC的備份集在單例項資料庫上恢復後，可以手工刪除多餘的那個undo表空間及其資料檔案，通常是undotbs2
3，redo問題
雙節點RAC的控制檔案的備份中記錄的是兩個例項的redo資訊，恢復完成後在open resetlogs開啟資料庫後回建立出thread2的online redo log，也就是節點2的聯機日誌。同undotbs2的處理原則一樣，刪除之。
1，RAC 端rman全備資料庫到/backup目錄下。
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 29 03:15:49 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=271163854)
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> backup tag 'full' format '/backup/full_%U.bak'
5> database include current controlfile;
6> sql 'alter system archive log current';
7> backup tag 'arch' format '/backup/arch_%U.arc'
8> archivelog all;
9> release channel c1;
10> release channel c2;
11> }
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
43 Full 522.99M DISK 00:00:04 2014-09-29 03:05:29
BP Key: 43 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /backup/full_1bpjmdbl_1_1.bak
List of Datafiles in backup set 43
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
2 Full 1298717 2014-09-29 03:05:25 +DATA/prod/datafile/sysaux.257.859325451
4 Full 1298717 2014-09-29 03:05:25 +DATA/prod/datafile/users.259.859325451
5 Full 1298717 2014-09-29 03:05:25 +DATA/prod/datafile/example.264.859325525
6 Full 1298717 2014-09-29 03:05:25 +DATA/prod/datafile/undotbs2.265.859325695
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
44 Full 649.16M DISK 00:00:04 2014-09-29 03:05:29
BP Key: 44 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /backup/full_1cpjmdbl_1_1.bak
List of Datafiles in backup set 44
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1298719 2014-09-29 03:05:25 +DATA/prod/datafile/system.256.859325451
3 Full 1298719 2014-09-29 03:05:25 +DATA/prod/datafile/undotbs1.258.859325451
7 Full 1298719 2014-09-29 03:05:25 +DATA/prod/datafile/goldengate.269.859338811
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
45 Full 80.00K DISK 00:00:00 2014-09-29 03:05:32
BP Key: 45 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /backup/full_1epjmdbs_1_1.bak << 引數檔案所在備份片，後面恢復時候需要用到
SPFILE Included: Modification time: 2014-09-29 02:02:18
SPFILE db_unique_name: PROD
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
46 Full 17.80M DISK 00:00:01 2014-09-29 03:05:33
BP Key: 46 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /backup/full_1dpjmdbs_1_1.bak << 控制檔案所在備份片，後面恢復時候需要用到
Control File Included: Ckp SCN: 1298731 Ckp time: 2014-09-29 03:05:32
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
47 53.63M DISK 00:00:00 2014-09-29 03:05:44
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: ARCH
Piece Name: /backup/arch_1gpjmdc8_1_1.arc
List of Archived Logs in backup set 47
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 28 1260638 2014-09-28 23:53:08 1260655 2014-09-28 23:53:11
1 29 1260655 2014-09-28 23:53:11 1298750 2014-09-29 03:05:34
1 30 1298750 2014-09-29 03:05:34 1298772 2014-09-29 03:05:40
2 24 1260645 2014-09-28 23:53:19 1260660 2014-09-28 23:53:22
2 25 1260660 2014-09-28 23:53:22 1298755 2014-09-29 03:05:42
2 26 1298755 2014-09-29 03:05:42 1298767 2014-09-29 03:05:45
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
48 62.20M DISK 00:00:00 2014-09-29 03:05:44
BP Key: 48 Status: AVAILABLE Compressed: NO Tag: ARCH
Piece Name: /backup/arch_1fpjmdc8_1_1.arc
List of Archived Logs in backup set 48
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 25 1211968 2014-09-28 19:28:25 1221060 2014-09-28 20:26:23
1 26 1221060 2014-09-28 20:26:23 1221084 2014-09-28 20:26:29
1 27 1221084 2014-09-28 20:26:29 1260638 2014-09-28 23:53:08
2 21 1211964 2014-09-28 19:28:23 1221067 2014-09-28 20:26:25
2 22 1221067 2014-09-28 20:26:25 1221089 2014-09-28 20:26:31
2 23 1221089 2014-09-28 20:26:31 1260645 2014-09-28 23:53:19
2，RAC端使用scp傳輸備份集到單例項端/home/oracle目錄下
[oracle@node1 ~]$ scp /backup/* oracle@172.16.228.8:/home/oracle
單例項端檢視接收到的備份集
[oracle@single ~]$ ls -l /home/oracle
total 1338624
-rw-r----- 1 oracle oinstall 65219584 Oct 8 21:39 arch_1fpjmdc8_1_1.arc
-rw-r----- 1 oracle oinstall 56237568 Oct 8 21:39 arch_1gpjmdc8_1_1.arc
-rw-r----- 1 oracle oinstall 548405248 Oct 8 21:39 full_1bpjmdbl_1_1.bak
-rw-r----- 1 oracle oinstall 680697856 Oct 8 21:40 full_1cpjmdbl_1_1.bak
-rw-r----- 1 oracle oinstall 18677760 Oct 8 21:40 full_1dpjmdbs_1_1.bak
-rw-r----- 1 oracle oinstall 98304 Oct 8 21:40 full_1epjmdbs_1_1.bak
3，單例項端使用RMAN從備份集中恢復pfile到/home/oracle/racpfile.ora
$ rman target /
RMAN> startup nomount;
RMAN> restore spfile to pfile '/home/oracle/racpfile.ora' from '/home/oracle/full_1epjmdbs_1_1.bak';
4，單例項端修改pfile檔案，將叢集相關的資訊全部註釋掉
[oracle@single ~]$ cat racpfile.ora
##PROD1.__db_cache_size=146800640
##PROD2.__db_cache_size=176160768
##PROD1.__java_pool_size=4194304
##PROD2.__java_pool_size=4194304
##PROD1.__large_pool_size=8388608
##PROD2.__large_pool_size=8388608
##PROD1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
##PROD1.__pga_aggregate_target=289406976
##PROD2.__pga_aggregate_target=293601280
##PROD1.__sga_target=385875968
##PROD2.__sga_target=381681664
##PROD1.__shared_io_pool_size=0
##PROD2.__shared_io_pool_size=0
##PROD1.__shared_pool_size=218103808
##PROD2.__shared_pool_size=184549376
##PROD1.__streams_pool_size=0
##PROD2.__streams_pool_size=0
##*.audit_file_dest='/u01/app/oracle/admin/PROD/adump'
*.audit_file_dest='/u01/admin/PROD/adump'
*.audit_trail='db'
##*.cluster_database=true << 叢集
*.compatible='11.2.0.4.0'
##*.control_files='+DATA/prod/controlfile/current.260.859325519','+ARCH/prod/controlfile/current.256.859325519'
*.control_files='+DATA/PROD/controlfile/control01.ctl','+FRA/PROD/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='PROD'
##*.db_recovery_file_dest='+ARCH'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4621074432
##*.diagnostic_dest='/u01/app/oracle'
*.diagnostic_dest='/u01/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
*.enable_goldengate_replication=TRUE
##PROD2.instance_number=2
##PROD1.instance_number=1
##*.memory_target=675282944
*.memory_target=583008256
*.open_cursors=300
*.processes=150
##*.remote_listener='cluster-scan:1521' << scan IP
*.remote_login_passwordfile='exclusive'
##PROD2.thread=2
##PROD1.thread=1
##PROD2.undo_tablespace='UNDOTBS2'
##PROD1.undo_tablespace='UNDOTBS1'
*.undo_tablespace='UNDOTBS1' << 此處只保留unodtbs1的引數，在後面的恢復中還是會將undotbs2給恢復出來。
5，單例項端SQLPLUS使用修改後的pfile檔案啟動例項到nomount狀態
SYS@PROD >startup nomount pfile=/home/oracle/racpfile.ora;
6，單例項端建立spfile到ASM磁碟組中的+DATA/PROD
SYS@PROD >create spfile='+DATA/PROD/spfilePROD.ora' from memory;
7，單例項端建立pfile，指引spfile的檔案位置
$ vi $ORACLE_HOME/dbs/initPROD.ora
spfile='+DATA/PROD/spfilePROD.ora'
8，單例項端使用RMAN啟動到nomount狀態
RMAN> startup force nomount;
RMAN> set DBID=271163854
9，單例項端使用RAMN從備份集中恢復控制檔案
RMAN> restore controlfile from '/home/oracle/full_1dpjmdbs_1_1.bak’;
10，單例項端mount資料庫
RMAN> mount database;
11，單例項端註冊備份集的路徑
RMAN> catalog start with '/home/oracle';
12，單例項端列出控制檔案中記錄的資料檔案
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name PROD
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** +DATA/prod/datafile/system.256.859325451
2 0 SYSAUX *** +DATA/prod/datafile/sysaux.257.859325451
3 0 UNDOTBS1 *** +DATA/prod/datafile/undotbs1.258.859325451
4 0 USERS *** +DATA/prod/datafile/users.259.859325451
5 0 EXAMPLE *** +DATA/prod/datafile/example.264.859325525
6 0 UNDOTBS2 *** +DATA/prod/datafile/undotbs2.265.859325695
7 0 GOLDENGATE *** +DATA/prod/datafile/goldengate.269.859338811
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/prod/tempfile/temp.263.859325523
13，單例項端根據列出的資料檔案採用set newname的方式來修改檔案路徑。
RMAN> run{
2> set newname for datafile 1 to '+DATA';
3> set newname for datafile 2 to '+DATA';
4> set newname for datafile 3 to '+DATA';
5> set newname for datafile 4 to '+DATA';
6> set newname for datafile 5 to '+DATA';
7> set newname for datafile 6 to '+DATA';
8> set newname for datafile 7 to '+DATA';
9> set newname for tempfile 1 to '+DATA’; <
10> restore database;
11> switch datafile all;
12> switch tempfile all;
13> recover database;
14> }
14，單例項端resetlogs方式開啟資料庫庫
RMAN> alter database open resetlogs;
15，單例項端檢視redo log的分佈情況，注意thread=2是RAC中第二個節點上的，單例項中不需要刻意刪除掉。
SYS@PROD >SELECT v$logfile.member, v$logfile.group#,thread#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024/1024 ,v$log.thread#
2 FROM v$log, v$logfile
3 WHERE v$log.group# = v$logfile.group#
4 ORDER BY v$log.thread#,v$logfile.group#;
MEMBER GROUP# THREAD# STATUS ARC V$LOG.BYTES/1024/1024 THREAD#
-------------------------------------------------- ---------- ---------- ---------------- --- --------------------- ----------
+FRA/prod/onlinelog/group_1.308.860453423 1 1 CURRENT NO 50 1
+DATA/prod/onlinelog/group_1.286.860453423 1 1 CURRENT NO 50 1
+FRA/prod/onlinelog/group_2.261.860453423 2 1 UNUSED YES 50 1
+DATA/prod/onlinelog/group_2.263.860453423 2 1 UNUSED YES 50 1
+DATA/prod/onlinelog/group_3.295.860453423 3 2 INACTIVE YES 50 2
+FRA/prod/onlinelog/group_3.260.860453423 3 2 INACTIVE YES 50 2
+DATA/prod/onlinelog/group_4.258.860453423 4 2 UNUSED YES 50 2
+FRA/prod/onlinelog/group_4.307.860453423 4 2 UNUSED YES 50 2
16，禁用thread 2
SYS@PROD >alter database disable thread 2;
17，刪除thread=2的日誌組group3和group4
SYS@PROD >alter database drop logfile group 3;
SYS@PROD >alter database drop logfile group 4;
刪除thread=2的日誌組後的redo log分佈
SYS@PROD >SELECT v$logfile.member, v$logfile.group#,thread#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024/1024 ,v$log.thread#
2 FROM v$log, v$logfile
3 WHERE v$log.group# = v$logfile.group#
4 ORDER BY v$log.thread#,v$logfile.group#;
MEMBER GROUP# THREAD# STATUS ARC V$LOG.BYTES/1024/1024 THREAD#
-------------------------------------------------- ---------- ---------- ---------------- --- --------------------- ----------
+DATA/prod/onlinelog/group_1.286.860453423 1 1 CURRENT NO 50 1
+FRA/prod/onlinelog/group_1.308.860453423 1 1 CURRENT NO 50 1
+DATA/prod/onlinelog/group_2.263.860453423 2 1 UNUSED YES 50 1
+FRA/prod/onlinelog/group_2.261.860453423 2 1 UNUSED YES 50 1
18，同樣思路，刪除不屬於單例項的undotbs2表空間及其資料檔案
SYS@PROD >col name for a50
SYS@PROD >select name from v$datafile;
NAME
--------------------------------------------------
+DATA/prod/datafile/system.296.860453223
+DATA/prod/datafile/sysaux.262.860453229
+DATA/prod/datafile/undotbs1.273.860453223
+DATA/prod/datafile/users.261.860453229
+DATA/prod/datafile/example.293.860453229
+DATA/prod/datafile/undotbs2.285.860453229 << 多餘的undotbs2
+DATA/prod/datafile/goldengate.259.860453223
7 rows selected.
SYS@PROD >select name from v$tablespace where name like 'UNDO%';
NAME
--------------------------------------------------
UNDOTBS1
UNDOTBS2
SYS@PROD >drop tablespace undotbs2 including contents and datafiles;
此次試驗結束
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1297860/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 在不同機器之間使用rman複製資料庫例項，從非asm到asm資料庫ASM
- 使用rman將資料庫遷移到ASM例項資料庫ASM
- 使用rman copy將資料庫遷移到ASM例項資料庫ASM
- Rman 單例項filesystem(Windows)恢復到ASM環境(Linux)單例WindowsASMLinux
- RAC asm恢復到單例項ASM單例
- RAC12.1.0.2.161018PSU從RAC+ASM恢復到單例項非ASM遇到的BUGASM單例
- ASM儲存使用RMAN複製控制檔案ASM
- rac asm 恢復到 單例項 1ASM單例
- rac asm 恢復到 單例項 2ASM單例
- RAC 例項 遷移到 單例項 -- 使用RMAN Duplicate單例
- rac到單例項的rman恢復單例
- 利用RMAN將資料庫從檔案系統遷移到ASM(單例項)資料庫ASM單例
- 如何複製控制檔案在ASM例項儲存ASM
- 單例項刪除ASM例項單例ASM
- RMAN異機恢復：RAC到單例項單例
- 恢復RAC資料庫到單例項（ASM）資料庫單例ASM
- oracle 11C rman 恢復到單例項Oracle單例
- Oracle 11.2.0.4 從單例項，使用RMAN 異機恢復到RACOracle單例
- 利用RMAN將非ASM檔案移動到ASM裡 - [ASM]ASM
- 10g rac asm 恢復到 單例項(二)ASM單例
- 10g rac asm 恢復到 單例項(一)ASM單例
- duplicate複製資料庫（rac-單例項）資料庫單例
- 簡單的單資料來源複製例項——流
- RAC+DG(asm單例項)ASM單例
- 單例項的duplicate(non ASM)單例ASM
- RAC恢復到單例項節點上單例
- 使用RMAN在ASM和檔案系統之間複製資料ASM
- RMAN高階應用之Duplicate複製資料庫(2)輔助例項資料庫
- 使用RMAN複製資料庫資料庫
- 單機環境配置ASM例項ASM
- 單節點執行ASM例項ASM
- 基本複製應用例項（轉）
- 【RMAN】使用RMAN duplicate複製同機資料庫資料庫
- Oracle單例項+ASM新增控制檔案Oracle單例ASM
- ASM之建立ASM例項ASM
- Oracle11g使用rman從單例項遷移到racOracle單例
- 將普通filesystem上的database移動到asm上DatabaseASM
- 使用普通檔案建立ASM例項ASM