使用rman备份将rac环境恢复到单实例

rac环境

[oracle@rac02 ~]$ cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

10.11.*.61 rac01

1.1.1.13 rac01-priv

10.11.*.200 rac01-vip

10.11.*.62 rac02

1.1.1.14 rac02-priv

10.11.*.201 rac02-vip

10.11.*.202 scanvip-ip

rac集群信息查看

# crsstat

ora.bol.db     ora....se.type 0/2    0/1    ONLINE    ONLINE    rac01

[oracle@rac02 ~]$ sqlplus / as sysdba

SQL> show parameter cluster

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

cluster_database             boolean     TRUE

cluster_database_instances         integer     2

cluster_interconnects             string

SQL> show parameter name

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

cell_offloadgroup_name             string

db_file_name_convert             string

db_name                  string     bol

db_unique_name                 string     bol

global_names                 boolean     FALSE

instance_name                 string     bol2

lock_name_space              string

log_file_name_convert             string

processor_group_name             string

service_names                 string     bol

SQL> archive log list;

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           +FRA/bol/arch

Oldest online log sequence     343

Next log sequence to archive   344

Current log sequence           344

SQL> set line 9999 pagesize 9999

SQL> col FILE_NAME format a60

SQL> select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile

union all

select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile

union all

select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile

union all

select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile

;

FILE_TYPE     FILE# FILE_NAME                            STATUS  ENABLED

----------- ---------- ------------------------------------------------------------ ------- ----------

datafile         1 +DATA/bol/datafile/system.259.1015241807             SYSTEM  READ WRITE

datafile         2 +DATA/bol/datafile/sysaux.260.1015241811             ONLINE  READ WRITE

datafile         3 +DATA/bol/datafile/undotbs1.261.1015241811            ONLINE  READ WRITE

datafile         4 +DATA/bol/datafile/undotbs2.263.1015241815            ONLINE  READ WRITE

datafile         5 +DATA/bol/datafile/users.264.1015241815                ONLINE  READ WRITE

datafile         6 +DATA/bol/datafile/cad01.dbf                    ONLINE  READ WRITE

datafile         7 +DATA/bol/datafile/scm01.dbf                    ONLINE  READ WRITE

datafile         8 +DATA/bol/datafile/zabbix01.dbf                    ONLINE  READ WRITE

tempfile         1 +DATA/bol/tempfile/temp.262.1015241813                ONLINE  READ WRITE

logfile          1 +DATA/bol/onlinelog/group_1.257.1015241807

logfile          1 +FRA/bol/onlinelog/group_1.257.1015241807

logfile          2 +DATA/bol/onlinelog/group_2.258.1015241807

logfile          2 +FRA/bol/onlinelog/group_2.258.1015241807

logfile          3 +DATA/bol/onlinelog/group_3.265.1015242985

logfile          3 +FRA/bol/onlinelog/group_3.259.1015242985

logfile          4 +DATA/bol/onlinelog/group_4.266.1015242985

logfile          4 +FRA/bol/onlinelog/group_4.260.1015242985

controlfile           +DATA/bol/controlfile/current.256.1015241807

controlfile           +FRA/bol/controlfile/current.256.1015241807

SQL> show parameter instance_n

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

instance_name                 string     bol2

instance_number              integer     2

SQL> show parameter spfile

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

spfile                     string     +DATA/bol/spfilebol.ora

查看rac02节点的rman备份

#ll -art /home/oracle/backup

-rw-r-----  1 oracle asmadmin   85729280 Dec  3 22:35 bol_fullbakepuihggn_1_1_20191203

-rw-r-----  1 oracle asmadmin 2966593536 Dec  3 22:35 bol_fullbakequihggo_1_1_20191203

-rw-r-----  1 oracle asmadmin  144319488 Dec  3 22:35 arch_esuihghs_1_1_20191203

-rw-r-----  1 oracle asmadmin   72366080 Dec  3 22:35 arch_etuihght_1_1_20191203

-rw-r-----  1 oracle asmadmin   18644992 Dec  3 22:35 20191203_BOL_478_1_4237955019.ctl

-rw-r-----  1 oracle asmadmin      98304 Dec  3 22:35 bol_spfile_evuihgi1_1_1_20191203

将rman备份文件cp到单实例环境（这里的单实例环境-只安装了数据库软件）

[oracle@rac02 backup]$ scp bol_fullbakepuihggn_1_1_20191203 bol_fullbakequihggo_1_1_20191203 arch_esuihghs_1_1_20191203 arch_etuihght_1_1_20191203 20191203_BOL_478_1_4237955019.ctl bol_spfile_evuihgi1_1_1_20191203 oracle@10.11.*.80:/home/oracle/backup/.

[oracle@oracle backup]$ cp bol_spfile_evuihgi1_1_1_20191203 initbol.ora

---修改参数文件

[oracle@oracle backup]$ more initbol.ora

*.audit_file_dest='/u01/app/oracle/admin/bol/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

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

*.db_block_size=8192

*.db_domain=''

*.db_name='bol'

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

*.db_recovery_file_dest_size=6005194752

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=bolXDB)'

*.log_archive_dest_1='location=/u01/app/oracle/archive'

*.log_archive_format='%t_%s_%r.arch'

*.memory_target=2147483648

*.open_cursors=300

*.processes=350

*.remote_login_passwordfile='exclusive'

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

---select 6744440832/1024/1024/1024,3*1024*1024*1024;

单实例库创建目录

mkdir -p /u01/app/oracle/fast_recovery_area/bol

mkdir -p /u01/app/oracle/oradata/bol/data

mkdir -p /u01/app/oracle/oradata/bol/tempfile

mkdir -p /u01/app/oracle/admin/bol/adump

mkdir -p /u01/app/oracle/admin/bol/data

mkdir -p /u01/app/oracle/admin/bol/redo

mkdir -p /u01/app/oracle/admin/bol/tempfile

mkdir -p  /u01/app/oracle/archive

单实例创建spfile文件

[oracle@oracle ~]$ vim .bash_profile

[oracle@oracle ~]$ source .bash_profile

[oracle@oracle ~]$ sqlplus -v

SQL*Plus: Release 11.2.0.4.0 Production

[oracle@oracle dbs]$ ORACLE_SID=bol

[oracle@oracle dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 4 11:30:49 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile='/home/oracle/backup/initbol.ora';

File created.

启动单实例到nomount状态

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Dec 4 11:35:07 2019

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area    2137886720 bytes

Fixed Size                     2254952 bytes

Variable Size               1342179224 bytes

Database Buffers             788529152 bytes

Redo Buffers                   4923392 bytes

从备份中恢复控制文件

==restore controlfile from '/home/oracle/backup/20191203_BOL_478_1_4237955019.ctl';

RMAN> restore controlfile from '/home/oracle/backup/20191203_BOL_478_1_4237955019.ctl';

Starting restore at 04-DEC-19

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=284 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/bol/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/bol/control02.ctl

Finished restore at 04-DEC-19

==控制文件已经还原，注意此处控制文件的还原路径是spfile中指定的路径，接下来还原数据文件及恢复数据库
==启动到mount状态并还原和恢复整个数据库

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> list backupset summary;

RMAN> list backupset of archivelog all;

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

477     69.01M     DISK        00:00:01     03-DEC-19

        BP Key: 477   Status: AVAILABLE  Compressed: NO  Tag: TAG20191203T223540

        Piece Name: /home/oracle/backup/arch_etuihght_1_1_20191203

  List of Archived Logs in backup set 477

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    433     10580072   03-DEC-19 10598676   04-DEC-19

  1    434     10598676   04-DEC-19 10598684   04-DEC-19

  2    341     10594034   04-DEC-19 10597753   04-DEC-19

  2    342     10597753   04-DEC-19 10598672   04-DEC-19

  2    343     10598672   04-DEC-19 10598689   04-DEC-19

==数据文件的转换

SQL> set pagesize  200 linesize 200

SQL> select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'

from v$datafile a

union all

select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'

from v$tempfile a

union all

SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || '''''  to  ''''' ||

a.MEMBER || ''''' ";'

FROM v$logfile a;

'SETNEWNAMEFORDATAFILE'||A.FILE#||'TO"'||A.NAME||'";'

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

set newname for datafile 1 to "+DATA/bol/datafile/system.259.1015241807";

set newname for datafile 2 to "+DATA/bol/datafile/sysaux.260.1015241811";

set newname for datafile 3 to "+DATA/bol/datafile/undotbs1.261.1015241811";

set newname for datafile 4 to "+DATA/bol/datafile/undotbs2.263.1015241815";

set newname for datafile 5 to "+DATA/bol/datafile/users.264.1015241815";

set newname for datafile 6 to "+DATA/bol/datafile/cad01.dbf";

set newname for datafile 7 to "+DATA/bol/datafile/scm01.dbf";

set newname for datafile 8 to "+DATA/bol/datafile/zabbix01.dbf";

set newname for tempfile 1 to "+DATA/bol/tempfile/temp.262.1015241813";

SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_1.257.1015241807''    to  ''+DATA/bol/onlinelog/group_1.257.1015241807'' ";

SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_1.257.1015241807''  to  ''+FRA/bol/onlinelog/group_1.257.1015241807'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_2.258.1015241807''    to  ''+DATA/bol/onlinelog/group_2.258.1015241807'' ";

SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_2.258.1015241807''  to  ''+FRA/bol/onlinelog/group_2.258.1015241807'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_3.265.1015242985''    to  ''+DATA/bol/onlinelog/group_3.265.1015242985'' ";

SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_3.259.1015242985''  to  ''+FRA/bol/onlinelog/group_3.259.1015242985'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_4.266.1015242985''    to  ''+DATA/bol/onlinelog/group_4.266.1015242985'' ";

SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_4.260.1015242985''  to  ''+FRA/bol/onlinelog/group_4.260.1015242985'' ";

17 rows selected.

RMAN> RUN{

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

set newname for datafile 1 to "/u01/app/oracle/oradata/bol/data/system01.dbf";

set newname for datafile 2 to "/u01/app/oracle/oradata/bol/data/sysaux02.dbf";

set newname for datafile 3 to "/u01/app/oracle/oradata/bol/data/undotbs101.dbf";

set newname for datafile 4 to "/u01/app/oracle/oradata/bol/data/undotbs202.dbf";

set newname for datafile 5 to "/u01/app/oracle/oradata/bol/data/users01.dbf";

set newname for datafile 6 to "/u01/app/oracle/oradata/bol/data/cad01.dbf";

set newname for datafile 7 to "/u01/app/oracle/oradata/bol/data/scm01.dbf";

set newname for datafile 8 to "/u01/app/oracle/oradata/bol/data/zabbix01.dbf";

set newname for tempfile 1 to "/u01/app/oracle/oradata/bol/tempfile/temp01.dbf";

SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_1.257.1015241807''    to  ''/u01/app/oracle/admin/bol/redo/redo01_1.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_1.257.1015241807''  to  ''/u01/app/oracle/admin/bol/redo/redo01_2.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_2.258.1015241807''    to  ''/u01/app/oracle/admin/bol/redo/redo02_1.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_2.258.1015241807''  to  ''/u01/app/oracle/admin/bol/redo/redo02_2.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_3.265.1015242985''    to  ''/u01/app/oracle/admin/bol/redo/redo03_1.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_3.259.1015242985''  to  ''/u01/app/oracle/admin/bol/redo/redo03_2.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_4.266.1015242985''    to  ''/u01/app/oracle/admin/bol/redo/redo04_1.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_4.260.1015242985''  to  ''/u01/app/oracle/admin/bol/redo/redo04_2.log'' ";

RESTORE DATABASE;

SWITCH DATAFILE ALL;

SWITCH TEMPFILE ALL;

}

==告警日志
==查看数据文件是否已经还原

[oracle@oracle ~]$ ll -h /u01/app/oracle/oradata/bol/data/

total 6.9G

-rw-r-----. 1 oracle oinstall 1.1G Dec  4 11:58 cad01.dbf

-rw-r-----. 1 oracle oinstall 1.1G Dec  4 11:58 scm01.dbf

-rw-r-----. 1 oracle oinstall 2.4G Dec  4 11:59 sysaux02.dbf

-rw-r-----. 1 oracle oinstall 761M Dec  4 11:59 system01.dbf

-rw-r-----. 1 oracle oinstall 1.2G Dec  4 11:58 undotbs101.dbf

-rw-r-----. 1 oracle oinstall 201M Dec  4 11:58 undotbs202.dbf

-rw-r-----. 1 oracle oinstall 5.1M Dec  4 11:58 users01.dbf

-rw-r-----. 1 oracle oinstall 501M Dec  4 11:58 zabbix01.dbf

==由前边的备份集中可以看出，备份集中的thread 1的最大日志号为434，thread 2的最大日志号为343，所以不完全恢复

RMAN> run{

2> set until sequence 434 thread 1;

3> set until sequence 343 thread 2;

4> recover database;

5> }

executing command: SET until clause

executing command: SET until clause

Starting recover at 04-DEC-19

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=284 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=433

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=342

channel ORA_DISK_1: reading from backup piece /home/oracle/backup/arch_etuihght_1_1_20191203

channel ORA_DISK_1: piece handle=/home/oracle/backup/arch_etuihght_1_1_20191203 tag=TAG20191203T223540

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/archive/2_342_1015241803.arch thread=2 sequence=342

archived log file name=/u01/app/oracle/archive/1_433_1015241803.arch thread=1 sequence=433

media recovery complete, elapsed time: 00:00:00

Finished recover at 04-DEC-19

使用resetlogs打开数据库

RMAN> alter database open resetlogs;

database opened

查看redo日志

[oracle@oracle backup]$ ll -h /u01/app/oracle/admin/bol/redo/

total 401M

-rw-r-----. 1 oracle oinstall 51M Dec  4 12:18 redo01_1.log

-rw-r-----. 1 oracle oinstall 51M Dec  4 12:18 redo01_2.log

-rw-r-----. 1 oracle oinstall 51M Dec  4 12:18 redo02_1.log

-rw-r-----. 1 oracle oinstall 51M Dec  4 12:18 redo02_2.log

-rw-r-----. 1 oracle oinstall 51M Dec  4 12:18 redo03_1.log

-rw-r-----. 1 oracle oinstall 51M Dec  4 12:18 redo03_2.log

-rw-r-----. 1 oracle oinstall 51M Dec  4 12:18 redo04_1.log

-rw-r-----. 1 oracle oinstall 51M Dec  4 12:18 redo04_2.log

查看相关文件

[oracle@oracle backup]$ sqlplus / as sysdba

SQL> show parameter name

SQL> archive log list;

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           /u01/app/oracle/archive

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL> set line 9999 pagesize 9999

SQL> col FILE_NAME format a60

SQL> select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile

union all

select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile

union all

select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile

union all

select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile

;

==清除未使用的redo

SQL> col instance format a8

SQL> select thread#,instance,status,enabled from v$thread;

   THREAD# INSTANCE STATUS ENABLED

---------- -------- ------ --------

     1 bol        OPEN   PUBLIC

     2 bol2     CLOSED PUBLIC

SQL> select group#,thread#,archived,status from v$log;

    GROUP#    THREAD# ARC STATUS

---------- ---------- --- ----------------

     1        1 NO  CURRENT

     2        1 YES UNUSED

     3        2 YES INACTIVE

     4        2 YES UNUSED

SQL> alter database disable thread 2;

Database altered.

SQL> select thread#,instance,status,enabled from v$thread;

   THREAD# INSTANCE STATUS ENABLED

---------- -------- ------ --------

     1 bol        OPEN   PUBLIC

     2 bol2     CLOSED DISABLED

==清除多余的 undo 文件

SQL> select name from v$tablespace where name like 'UNDO%';

NAME

------------------------------

UNDOTBS1

UNDOTBS2

SQL> show parameter undo_tablespace;

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace              string     UNDOTBS1

SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

到此，恢复以及完成。迁移还可以使用在线 RMAN Duplicate

参考：
https://www.cnblogs.com/lhrbest/p/4546661.html

巴特西

使用rman备份将rac环境恢复到单实例

最新文章

热门文章