使用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

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

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

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

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

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

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

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/* [email protected]:/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'

*.remote_login_passwordfile='exclusive'

##PROD2.thread=2

##PROD1.thread=1

##PROD2.undo_tablespace='UNDOTBS2'

##PROD1.undo_tablespace='UNDOTBS1'

*.undo_tablespace='UNDOTBS1'

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的方式來修改檔案路徑。

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分布

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

+DATA/prod/datafile/goldengate.259.860453223

7 rows selected.

SYS@PROD >select name from v$tablespace where name like 'UNDO%';

UNDOTBS1

UNDOTBS2

SYS@PROD >drop tablespace undotbs2 including contents and datafiles;

此次試驗結束

使用RMAN将RAC+ASM複制到單執行個體+ASM上

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List