RAC恢復到單機

參考文件 http://blog.itpub.net/30126024/viewspace-2144906/

背景：有一套rac，也配置了dg，現在想透過rac的備份在一臺單機上恢復，單機上裝了同版本資料庫，只是裝了軟體，沒有建庫。

1、RAC主庫備份

RMAN> backup database format '/home/oracle/rmanbackup/full_%U.bak' plus archivelog format '/home/oracle/rmanbackup/arch_%U.bak';

2、把主庫備份複製到單機的目錄/home/oracle/rmanbackup/

3、rac透過spfile建立pfile，傳到單機並修改相應的引數。

SQL> create pfile='/home/oracle/rmanbackup/initprod1.ora' from spfile;

修改前rac匯出來的pfile

[oracle@rac1:/home/oracle/rmanbackup]$cat initprod1.ora 
prod2.__db_cache_size=1778384896
prod1.__db_cache_size=1778384896
prod2.__java_pool_size=16777216
prod1.__java_pool_size=16777216
prod2.__large_pool_size=33554432
prod1.__large_pool_size=33554432
prod1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod2.__pga_aggregate_target=1577058304
prod1.__pga_aggregate_target=1577058304
prod2.__sga_target=2348810240
prod1.__sga_target=2348810240
prod2.__shared_io_pool_size=0
prod1.__shared_io_pool_size=0
prod2.__shared_pool_size=486539264
prod1.__shared_pool_size=486539264
prod2.__streams_pool_size=0
prod1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATADG01/prod/controlfile/current.260.1083328405','+DATADG02/prod/controlfile/current.256.1083328405'
*.db_block_size=8192
*.db_create_file_dest='+DATADG01'
*.db_create_online_log_dest_1='+DATADG01'
*.db_create_online_log_dest_2='+DATADG02'
*.db_domain=''
*.db_file_name_convert='+DATADG01/','+DATADG01/','+DATADG02/','+DATADG02/'
*.db_name='prod'
*.db_recovery_file_dest='+ARCHDG01'
*.db_recovery_file_dest_size=4621074432
*.db_unique_name='prod'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.fal_client='tns_primary'
*.fal_server='tns_standby'
prod1.instance_number=1
prod2.instance_number=2
*.log_archive_config='DG_CONFIG=(prod,prodstd)'
*.log_archive_dest_1='LOCATION=+ARCHDG01/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'
*.log_archive_dest_2='SERVICE=tns_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prodstd'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=8
*.log_file_name_convert='+DATADG01/','+DATADG01/','+DATADG02/','+DATADG02/'
*.memory_target=3922722816
*.open_cursors=300
*.processes=1000
*.remote_listener='cluster-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.standby_file_management='AUTO'
prod2.thread=2
prod1.thread=1
prod2.undo_tablespace='UNDOTBS2'
prod1.undo_tablespace='UNDOTBS1'

修改後為，放到單機的$ORACLE_HOME/dbs/目錄下

[oracle@dbserver dbs]$ cat initprod1.ora 
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/control01.ctl','/u01/app/oracle/oradata/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/'
*.db_create_online_log_dest_1='/u01/app/oracle/oradata/'
*.db_create_online_log_dest_2='/u01/app/oracle/oradata/'
*.db_domain=''
*.db_file_name_convert='+DATADG01/','/u01/app/oracle/oradata/','+DATADG02/','/u01/app/oracle/oradata/'
*.db_name='prod'
*.db_recovery_file_dest='/u01/app/oracle/oradata/'
*.db_recovery_file_dest_size=4621074432
*.db_unique_name='prod'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod1XDB)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=8
*.log_file_name_convert='+DATADG01/','/u01/app/oracle/oradata/','+ARCHDG/','/u01/app/oracle/oradata/','+DATADG02/','/u01/app/oracle/oradata/'
*.memory_target=3922722816
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1105

注意幾個點

*.control_files=' /u01/app/oracle/oradata/control01.ctl','/u01/app/oracle/oradata/control02.ctl'

*.db_create_file_dest=' /u01/app/oracle/oradata/'

*.db_create_online_log_dest_1=' /u01/app/oracle/oradata/'

*.db_create_online_log_dest_2=' /u01/app/oracle/oradata/'

*.db_file_name_convert='+DATADG01/',' /u01/app/oracle/oradata/','+DATADG02/',' /u01/app/oracle/oradata/'

*.db_recovery_file_dest=' /u01/app/oracle/oradata/'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod 1XDB)'

*.log_archive_dest_1='LOCATION= /u01/app/oracle/oradata/'

*.log_file_name_convert='+DATADG01/',' /u01/app/oracle/oradata/','+DATADG02/',' /u01/app/oracle/oradata/'

db_file_name_convert， log_file_name_convert可以不配置，rman恢復的時候指定，參考文章頭部給的連結。

4、確保引數中的目錄在單機中存在且可訪問

[oracle@dbserver dbs]$ mkdir -p /u01/app/oracle/admin/prod/adump
[oracle@dbserver dbs]$ mkdir -p /u01/app/oracle/oradata/

5、單機建立密碼檔案

orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprod1 password=oracle entries=10

6、單機透過pfile啟動到nomount狀態，並透過pfile建立spfile，並從spfile重新啟動到nomount。

7、單機rman恢復複製過來的控制檔案,並catalog所有複製過來的檔案

RMAN> restore controlfile from '/home/oracle/rmanbackup/full_0j0a1s6a_1_1.bak';
Starting restore at 2021-09-26 15:58:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=771 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/control01.ctl
output file name=/u01/app/oracle/oradata/control02.ctl
Finished restore at 2021-09-26 15:58:12

切換到mount狀態，catalog所有複製過來的檔案

RMAN> alter database mount;
RMAN> catalog start with '/home/oracle/rmanbackup/';

檢視擁有的備份。

RMAN> list backup;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
1       Incr 0  1.10G      DISK        00:00:07     2021-09-26 09:03:06
        BP Key: 1   Status: EXPIRED  Compressed: NO  Tag: TAG20210926T090259
        Piece Name: /home/oracle/rmanbackup/orcl_full_0e0a15m3_1_1
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 1161718    2021-09-26 09:02:59 +DATADG01/prod/datafile/system.256.1083328327
  2    0  Incr 1161718    2021-09-26 09:02:59 +DATADG01/prod/datafile/sysaux.257.1083328327
  3    0  Incr 1161718    2021-09-26 09:02:59 +DATADG01/prod/datafile/undotbs1.258.1083328327
  4    0  Incr 1161718    2021-09-26 09:02:59 +DATADG01/prod/datafile/users.259.1083328327
  5    0  Incr 1161718    2021-09-26 09:02:59 +DATADG01/prod/datafile/example.264.1083328433
  6    0  Incr 1161718    2021-09-26 09:02:59 +DATADG01/prod/datafile/undotbs2.265.1083328627
  7    0  Incr 1161718    2021-09-26 09:02:59 +DATADG01/prod/datafile/gac.277.1084032029
  8    0  Incr 1161718    2021-09-26 09:02:59 +DATADG02/prod/datafile/haifeng.269.1084032067
  9    0  Incr 1161718    2021-09-26 09:02:59 +DATADG02/prod/datafile/shaot.270.1084035817
  10   0  Incr 1161718    2021-09-26 09:02:59 +DATADG01/prod/datafile/test.278.1084036129
BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
2       Incr 0  17.98M     DISK        00:00:02     2021-09-26 09:03:16
        BP Key: 2   Status: EXPIRED  Compressed: NO  Tag: TAG20210926T090259
        Piece Name: /home/oracle/rmanbackup/orcl_full_0f0a15mi_1_1
  SPFILE Included: Modification time: 2021-09-26 08:56:41
  SPFILE db_unique_name: PROD
  Control File Included: Ckp SCN: 1161739      Ckp time: 2021-09-26 09:03:14

注意到，識別的控制檔案裡面記載的目錄還是在asm中，但是我們spfile裡面的引數已經配置了轉換，所以可以直接恢復資料庫。

RMAN> restore database;
Starting restore at 2021-09-26 16:03:12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATADG01/prod/datafile/system.256.1083328327
channel ORA_DISK_1: restoring datafile 00002 to +DATADG01/prod/datafile/sysaux.257.1083328327
channel ORA_DISK_1: restoring datafile 00003 to +DATADG01/prod/datafile/undotbs1.258.1083328327
channel ORA_DISK_1: restoring datafile 00004 to +DATADG01/prod/datafile/users.259.1083328327
channel ORA_DISK_1: restoring datafile 00005 to +DATADG01/prod/datafile/example.264.1083328433
channel ORA_DISK_1: restoring datafile 00006 to +DATADG01/prod/datafile/undotbs2.265.1083328627
channel ORA_DISK_1: restoring datafile 00007 to +DATADG01/prod/datafile/gac.277.1084032029
channel ORA_DISK_1: restoring datafile 00008 to +DATADG02/prod/datafile/haifeng.269.1084032067
channel ORA_DISK_1: restoring datafile 00009 to +DATADG02/prod/datafile/shaot.270.1084035817
channel ORA_DISK_1: restoring datafile 00010 to +DATADG01/prod/datafile/test.278.1084036129
channel ORA_DISK_1: reading from backup piece /home/oracle/rmanbackup/full_0i0a1s5q_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/rmanbackup/full_0i0a1s5q_1_1.bak tag=TAG20210926T152650
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 2021-09-26 16:03:48
RMAN>

檔案就會自動轉換。

RMAN> recover database;

8、開啟資料庫，查詢，發現檔案已經自動轉化了。

SQL> alter database open resetlogs;
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_jo0c4fkn_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_jo0c4fkq_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_jo0c4fl5_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_users_jo0c4h5n_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_example_jo0c4fks_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs2_jo0c4gmt_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_gac_jo0c4fkv_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_haifeng_jo0c4fkx_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_shaot_jo0c4fl0_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_test_jo0c4fl2_.dbf
10 rows selected.