Oracle11gR2 RAC迁移到单实例

Oracle11g RAC数据库迁移到单机

一、环境

1.RAC环境(源库)

HostName	Public IP	Private IP	VIP	Scan IP	DB Name
Rac01	192.168.1.21	10.0.0.21	192.168.1.23	192.168.1.25	mydb
Rac02	192.168.1.22	10.0.0.22	192.168.1.24		mydb

2.单机环境(目标库)

HostName	IP	DB Name
server01	192.168.1.112	ora11g

二、源库创建pfile

将创建的pfile文件传到目标数据库的$ORACLE_HOME/dbs/ 目录下， 我们目标库为ora11g，pfile改名为initora11g.ora

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      mydb

SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

三、传输pfile至目标库

[oracle@server01 tmp]$ scp pfile.ora 192.168.1.112:/opt/oracle11g/product/11.2.0/db_1/dbs

四、RMAN备份源库

备份源库，并将备份文件传到目标库，最好位置相同

[oracle@server01 ~]$ mkdir /tmp/rman
[oracle@server01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 27 19:17:02 2021

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

connected to target database: MYDB (DBID=2999538401)

RMAN> run{
allocate channel d1 type disk;
allocate channel d2 type disk;
backup database include current controlfile format '/tmp/rman/full_%d_%s_%T_%U';
sql 'alter system archive log current';
backup archivelog all format '/tmp/rman/log_%d_%s_%T_%U' delete all input;
crosscheck archivelog all;
crosscheck backup;
crosscheck copy;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog all;
delete noprompt copy;
release channel d2;
release channel d1;
}

五、Copy备份至目标库

[oracle@server01 ~]$ scp -r /tmp/rman 192.168.1.112:/tmp

六、目标库恢复

原则上目标库只需要安装Oracle Home，然后创建pfile中所需目录即可，无需创建数据库，目标库如果已存在，则需要手工删除数据文件，参数文件、密码文件等

1.Duplicate方法恢复

1.关闭目标数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2.卸载数据库或手工删除数据
[oracle@server dbs]$ cd /opt/Ora11gData/ora11g/
[oracle@server ora11g]$ ll
total 1640456
-rw-r----- 1 oracle oinstall   9781248 Dec 27 19:42 control01.ctl
-rw-r----- 1 oracle oinstall   9781248 Dec 27 19:42 control02.ctl
-rw-r----- 1 oracle oinstall  52429312 Dec 27 17:11 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Dec 27 17:11 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Dec 27 19:42 redo03.log
-rw-r----- 1 oracle oinstall 597696512 Dec 27 19:42 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Dec 27 19:42 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Dec 27 18:11 temp01.dbf
-rw-r----- 1 oracle oinstall  78651392 Dec 27 19:42 undotbs01.dbf
-rw-r----- 1 oracle oinstall  26222592 Dec 27 19:42 users01.dbf
[oracle@server ora11g]$ rm -rf *

[oracle@server ora11g]$ cd $ORACLE_HOME/dbs
[oracle@server dbs]$ rm -rf spfileora11g.ora
[oracle@server dbs]$ rm -rf orapwora11g

3.创建口令文件
orapwd file=orapwora11g password=oracle

4.创建所需目录
[oracle@server dbs]$ mkdir -p /opt/Ora11gData/flash_recovery_area

5.修改参数文件
[oracle@server dbs]$ cp pfile.ora initora11g.ora
源库参数如下:
[oracle@server dbs]$ more pfile.ora
mydb1.__db_cache_size=339738624
mydb2.__db_cache_size=339738624
mydb1.__java_pool_size=4194304
mydb2.__java_pool_size=4194304
mydb1.__large_pool_size=8388608
mydb2.__large_pool_size=8388608
mydb1.__pga_aggregate_target=192937984
mydb2.__pga_aggregate_target=192937984
mydb1.__sga_target=570425344
mydb2.__sga_target=570425344
mydb1.__shared_io_pool_size=0
mydb2.__shared_io_pool_size=0
mydb1.__shared_pool_size=209715200
mydb2.__shared_pool_size=209715200
mydb1.__streams_pool_size=0
mydb2.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/mydb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/mydb/controlfile/current.256.1084542565','+FRA/mydb/controlfile/current.256.1084542567'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='mydb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mydbXDB)'
mydb1.instance_number=1
mydb2.instance_number=2
*.open_cursors=300
*.pga_aggregate_target=189792256
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=570425344
mydb2.thread=2
mydb1.thread=1
mydb2.undo_tablespace='UNDOTBS2'
mydb1.undo_tablespace='UNDOTBS1'


修改后参数如下:
[oracle@server dbs]$ vi initora11g.ora
ora11g.__db_cache_size=339738624
ora11g.__java_pool_size=4194304
ora11g.__large_pool_size=8388608
ora11g.__pga_aggregate_target=192937984
ora11g.__sga_target=570425344
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=209715200
ora11g.__streams_pool_size=0
*.audit_file_dest='/opt/oracle11g/admin/ora11g/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.4.0'
*.control_files='/opt/Ora11gData/ora11g/control01.ctl','/opt/Ora11gData/ora11g/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/opt/Ora11gData/ora11g/'
*.db_domain=''
*.db_name='ora11g'
*.db_recovery_file_dest='/opt/Ora11gData/flash_recovery_area'
*.db_recovery_file_dest_size=4005194752
*.diagnostic_dest='/opt/oracle11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.open_cursors=300
*.pga_aggregate_target=189792256
*.processes=150
*.remote_login_passwordfile='exclusive'
*.undo_management='AUTO'
*.sga_target=570425344
*.undo_tablespace='UNDOTBS1'
*.log_archive_format='%t_%s_%r.dbf'


*.log_file_name_convert=('+DATA/mydb/onlinelog','/opt/Ora11gData/ora11g','+FRA/mydb/onlinelog','/opt/Ora11gData/ora11g')
*.db_file_name_convert=('+DATA/mydb/datafile','/opt/Ora11gData/ora11g','+DATA/mydb/tempfile','/opt/Ora11gData/ora11g')

 
 

6.目标库创建tnsnames.ora
创建连接到源库的tnsnames:
RAC_MYDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.25)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = MYDB)
    )
  )

7.将目标库启动到nomount 
[oracle@server dbs]$ export ORACLE_SID=ora11g
[oracle@server dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 27 20:16:53 2021

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  567869440 bytes
Fixed Size                  2255272 bytes
Variable Size             222299736 bytes
Database Buffers          335544320 bytes
Redo Buffers                7770112 bytes


8.Duplicate Database
[oracle@server dbs]$ rman target sys/oracle@RAC_MYDB auxiliary /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 27 20:23:59 2021

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

connected to target database: MYDB (DBID=2999538401)
connected to auxiliary database: ORA11G (not mounted)

RMAN> duplicate target database to ora11g;

2.手工恢复方法

假定目标库只安装了Oracle Home，没有DBCA创建数据库

(1)修改pfile
[oracle@server01 ~]$ cd $ORACLE_HOME/dbs
[oracle@server01 dbs]$ ll
total 8
-rw-r-----. 1 oracle oinstall   35 Dec 30 14:09 pfile.ora
-rw-r--r--. 1 oracle oinstall 2851 May 15  2009 init.ora

[oracle@server01 dbs]$ cp pfile.ora initmydb.ora

源库pfile.ora内容:
[oracle@server01 dbs]$ more pfile.ora 

mydb1.__db_cache_size=339738624
mydb2.__db_cache_size=339738624
mydb1.__java_pool_size=4194304
mydb2.__java_pool_size=4194304
mydb1.__large_pool_size=8388608
mydb2.__large_pool_size=8388608
mydb1.__pga_aggregate_target=192937984
mydb2.__pga_aggregate_target=192937984
mydb1.__sga_target=570425344
mydb2.__sga_target=570425344
mydb1.__shared_io_pool_size=0
mydb2.__shared_io_pool_size=0
mydb1.__shared_pool_size=209715200
mydb2.__shared_pool_size=209715200
mydb1.__streams_pool_size=0
mydb2.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/mydb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/mydb/controlfile/current.256.1084542565','+FRA/mydb/controlfile
/current.256.1084542567'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='mydb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mydbXDB)'
mydb1.instance_number=1
mydb2.instance_number=2
*.open_cursors=300
*.pga_aggregate_target=189792256
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=570425344
mydb2.thread=2
mydb1.thread=1
mydb2.undo_tablespace='UNDOTBS2'
mydb1.undo_tablespace='UNDOTBS1'


目标库修改后参数内容:
[oracle@server01 dbs]$ vi initoradb.ora

mydb.__db_cache_size=339738624
mydb.__java_pool_size=4194304
mydb.__large_pool_size=8388608
mydb.__pga_aggregate_target=192937984
mydb.__sga_target=570425344
mydb.__shared_io_pool_size=0
mydb.__shared_pool_size=209715200
mydb.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/mydb/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.4.0'
*.control_files='/opt/app/oracle/oradata/mydb/control01.ctl','/opt/app/oracle/oradata/mydb/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/opt/app/oracle/oradata/mydb'
*.db_domain=''
*.db_name='mydb'
*.db_recovery_file_dest='/opt/app/oracle/oradata/flash_recovery_area'
*.db_recovery_file_dest_size=4005194752
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mydbXDB)'
*.open_cursors=300
*.pga_aggregate_target=189792256
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=570425344
*.undo_tablespace='UNDOTBS1'
*.log_archive_format='%t_%s_%r.dbf'

*.log_file_name_convert=('+DATA/mydb/onlinelog','/opt/app/oracle/oradata/mydb','+FRA/mydb/onlinelog','/opt/app/oracle/oradata/mydb')
*.db_file_name_convert=('+DATA/mydb/datafile','/opt/app/oracle/oradata/mydb','+DATA/mydb/tempfile','/opt/app/oracle/oradata/mydb')
 

(2)创建密码文件
orapwd file=orapworadb password=oracle entries=5

(3)创建所需要目录
[oracle@server01 dbs]$ mkdir -p /opt/app/oracle/admin/mydb/adump
[oracle@server01 dbs]$ mkdir -p /opt/app/oracle/oradata/mydb
[oracle@server01 dbs]$ mkdir -p /opt/app/oracle/oradata/flash_recovery_area

(4)启动数据库至nomount
[oracle@server01 ~]$ ll /tmp/rman
total 1133260
-rw-r-----. 1 oracle oinstall 1130889216 Dec 30 13:59 full_MYDB_1_20211227_010hq4u6_1_1
-rw-r-----. 1 oracle oinstall    4644864 Dec 30 13:59 full_MYDB_2_20211227_020hq4u7_1_1
-rw-r-----. 1 oracle oinstall   18546688 Dec 30 13:59 full_MYDB_3_20211227_030hq4v2_1_1
-rw-r-----. 1 oracle oinstall      98304 Dec 30 13:59 full_MYDB_4_20211227_040hq4vd_1_1
-rw-r-----. 1 oracle oinstall    6271488 Dec 30 13:59 log_MYDB_6_20211227_060hq518_1_1
-rw-r-----. 1 oracle oinstall       2560 Dec 30 13:59 log_MYDB_7_20211227_070hq51a_1_1

[oracle@server01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 30 19:43:44 2021

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  567869440 bytes
Fixed Size                  2255272 bytes
Variable Size             222299736 bytes
Database Buffers          339738624 bytes
Redo Buffers                3575808 bytes
SQL> 

(5)恢复控制文件
[oracle@server01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 30 19:52:12 2021

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

connected to target database: ORADB (not mounted)

RMAN> restore controlfile from '/tmp/rman/full_MYDB_3_20211227_030hq4v2_1_1';

Starting restore at 30-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/oradb/control01.ctl
output file name=/opt/app/oracle/oradata/oradb/control02.ctl
Finished restore at 30-DEC-21

(6)启动数据库至mount
[oracle@server01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 30 19:54:07 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database mount;

Database altered.

(7)恢复数据库
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name MYDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     +DATA/mydb/datafile/system.259.1084542583
2    0        SYSAUX               ***     +DATA/mydb/datafile/sysaux.260.1084542629
3    0        UNDOTBS1             ***     +DATA/mydb/datafile/undotbs1.261.1084542659
4    0        UNDOTBS2             ***     +DATA/mydb/datafile/undotbs2.263.1084542685
5    0        USERS                ***     +DATA/mydb/datafile/users.264.1084542699
6    0        TEST                 ***     +DATA/mydb/datafile/test.268.1092418937

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/mydb/tempfile/temp.262.1084542667


SQL> set linesize 500
SQL> set pagesize 500
SQL> col file_name for a60
SQL> select 'set newname for datafile '||file_id||' to ''/opt/app/oracle/oradata/mydb/'||substr(file_name, instr(file_name, '/', -1) + 1) || ''';' from dba_data_files;

set newname for datafile 1 to '/opt/app/oracle/oradata/mydb/system.259.1084542583';
set newname for datafile 2 to '/opt/app/oracle/oradata/mydb/sysaux.260.1084542629';
set newname for datafile 3 to '/opt/app/oracle/oradata/mydb/undotbs1.261.1084542659';
set newname for datafile 4 to '/opt/app/oracle/oradata/mydb/undotbs2.263.1084542685';
set newname for datafile 5 to '/opt/app/oracle/oradata/mydb/users.264.1084542699';
set newname for datafile 6 to '/opt/app/oracle/oradata/mydb/test.268.1092418937';

run{
   allocate channel t1 device type disk;
   allocate channel t2 device type disk;
   set newname for datafile 1 to '/opt/app/oracle/oradata/mydb/system.259.1084542583';
   set newname for datafile 2 to '/opt/app/oracle/oradata/mydb/sysaux.260.1084542629';
   set newname for datafile 3 to '/opt/app/oracle/oradata/mydb/undotbs1.261.1084542659';
   set newname for datafile 4 to '/opt/app/oracle/oradata/mydb/undotbs2.263.1084542685';
   set newname for datafile 5 to '/opt/app/oracle/oradata/mydb/users.264.1084542699';
   set newname for datafile 6 to '/opt/app/oracle/oradata/mydb/test.268.1092418937';
   restore database;
   switch datafile all;
   recover database;
   release channel t1;
   release channel t2;
  }
  
  
[oracle@server01 dbs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 31 18:45:44 2021

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

connected to target database: MYDB (DBID=2999538401, not open)

RMAN> run{
   allocate channel t1 device type disk;
   allocate channel t2 device type disk;
   set newname for datafile 1 to '/opt/app/oracle/oradata/mydb/system.259.1084542583';
   set newname for datafile 2 to '/opt/app/oracle/oradata/mydb/sysaux.260.1084542629';
   set newname for datafile 3 to '/opt/app/oracle/oradata/mydb/undotbs1.261.1084542659';
   set newname for datafile 4 to '/opt/app/oracle/oradata/mydb/undotbs2.263.1084542685';
   set newname for datafile 5 to '/opt/app/oracle/oradata/mydb/users.264.1084542699';
   set newname for datafile 6 to '/opt/app/oracle/oradata/mydb/test.268.1092418937';
   restore database;
   switch datafile all;
   recover database;
   release channel t1;
   release channel t2;
   }

allocated channel: t1
channel t1: SID=17 device type=DISK

allocated channel: t2
channel t2: SID=1 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 31-DEC-21

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00004 to /opt/app/oracle/oradata/mydb/undotbs2.263.1084542685
channel t1: restoring datafile 00005 to /opt/app/oracle/oradata/mydb/users.264.1084542699
channel t1: restoring datafile 00006 to /opt/app/oracle/oradata/mydb/test.268.1092418937
channel t1: reading from backup piece /tmp/rman/full_MYDB_2_20211227_020hq4u7_1_1
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00001 to /opt/app/oracle/oradata/mydb/system.259.1084542583
channel t2: restoring datafile 00002 to /opt/app/oracle/oradata/mydb/sysaux.260.1084542629
channel t2: restoring datafile 00003 to /opt/app/oracle/oradata/mydb/undotbs1.261.1084542659
channel t2: reading from backup piece /tmp/rman/full_MYDB_1_20211227_010hq4u6_1_1
channel t1: piece handle=/tmp/rman/full_MYDB_2_20211227_020hq4u7_1_1 tag=TAG20211227T191726
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:56
channel t2: piece handle=/tmp/rman/full_MYDB_1_20211227_010hq4u6_1_1 tag=TAG20211227T191726
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:01:06
Finished restore at 31-DEC-21

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1092768444 file name=/opt/app/oracle/oradata/mydb/system.259.1084542583
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1092768444 file name=/opt/app/oracle/oradata/mydb/sysaux.260.1084542629
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1092768444 file name=/opt/app/oracle/oradata/mydb/undotbs1.261.1084542659
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1092768444 file name=/opt/app/oracle/oradata/mydb/undotbs2.263.1084542685
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=1092768444 file name=/opt/app/oracle/oradata/mydb/users.264.1084542699
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=1092768444 file name=/opt/app/oracle/oradata/mydb/test.268.1092418937

Starting recover at 31-DEC-21

starting media recovery

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=2 sequence=18
channel t1: restoring archived log
archived log thread=1 sequence=86
channel t1: restoring archived log
archived log thread=1 sequence=87
channel t1: reading from backup piece /tmp/rman/log_MYDB_6_20211227_060hq518_1_1
channel t2: starting archived log restore to default destination
channel t2: restoring archived log
archived log thread=2 sequence=19
channel t2: reading from backup piece /tmp/rman/log_MYDB_7_20211227_070hq51a_1_1
channel t1: piece handle=/tmp/rman/log_MYDB_6_20211227_060hq518_1_1 tag=TAG20211227T191903
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=/tmp/1_86_1084542561.dbf thread=1 sequence=86
archived log file name=/tmp/2_18_1084542561.dbf thread=2 sequence=18
archived log file name=/tmp/1_87_1084542561.dbf thread=1 sequence=87
channel t2: piece handle=/tmp/rman/log_MYDB_7_20211227_070hq51a_1_1 tag=TAG20211227T191903
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:02
archived log file name=/tmp/2_19_1084542561.dbf thread=2 sequence=19
unable to find archived log
archived log thread=1 sequence=88
released channel: t1
released channel: t2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/31/2021 18:47:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 88 and starting SCN of 1371411


(8)打开数据库
[oracle@server01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 31 18:54:01 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open resetlogs;

Database altered.

七、恢复后处理事宜

1.清理多余的undo文件
SQL> select name from v$tablespace where name like 'UNDO%';

NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL> col name for a50
SQL> select * from v$dbfile;

     FILE# NAME
---------- --------------------------------------------------
         1 /opt/Ora11gData/ora11g/system.259.1084542583
         2 /opt/Ora11gData/ora11g/sysaux.260.1084542629
         3 /opt/Ora11gData/ora11g/undotbs1.261.1084542659
         4 /opt/Ora11gData/ora11g/undotbs2.263.1084542685
         5 /opt/Ora11gData/ora11g/users.264.1084542699
         6 /opt/Ora11gData/ora11g/test.268.1092418937

SQL> show parameter undo_tablespace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

2.清除未使用线程的redo日志组
默认情况下，RAC 环境下，每个实例都有2个redo。 在单实例下，就没有必要了。 我们删除点线程2的redo 信息。
SQL> select thread#,status,enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC

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

    GROUP#    THREAD# ARC STATUS
---------- ---------- --- ----------------
         1          1 YES INACTIVE
         2          1 NO  CURRENT
         3          2 YES UNUSED
         4          2 YES UNUSED
         
         
SQL> alter database disable thread 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL>  alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

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

    GROUP#    THREAD# ARC STATUS
---------- ---------- --- ----------------
         1          1 YES INACTIVE
         2          1 NO  CURRENT

再添加一个redo
alter database add logfile group 3 ('/opt/Ora11gData/ora11g/redo3.log') size 50m;

3.重建临时表空间

八、错误处理

1.ORA-00349: failure obtaining block size for '+dat

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     567869440 bytes

Fixed Size                     2255272 bytes
Variable Size                222299736 bytes
Database Buffers             335544320 bytes
Redo Buffers                   7770112 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORA11G" RESETLOGS ARCHIVELOG 
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+data', '/opt/Ora11gData/ora11g/group_1.257.1084542569' ) SIZE 50 M  REUSE,
  GROUP   2 ( '+data', '/opt/Ora11gData/ora11g/group_2.258.1084542579' ) SIZE 50 M  REUSE
 DATAFILE
  '/opt/Ora11gData/ora11g/system.259.1084542583'
 CHARACTER SET AL32UTF8

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/27/2021 20:57:30
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-00349: failure obtaining block size for '+dat


解决办法:
SQL> set linesize 200 pagesize 200
SQL> col member format a60
SQL> select v1.thread#, v1.group#, member, sequence#, first_change#, archived, v1.status from v$log v1, v$logfile v2 where v1.group#=v2.group#;

   THREAD#     GROUP# MEMBER                                                        SEQUENCE# FIRST_CHANGE# ARC STATUS
---------- ---------- ------------------------------------------------------------ ---------- ------------- --- ----------------
         1          1 +DATA/mydb/onlinelog/group_1.257.1084542567                          93       1464214 NO  CURRENT
         1          1 +FRA/mydb/onlinelog/group_1.257.1084542569                           93       1464214 NO  CURRENT
         1          2 +DATA/mydb/onlinelog/group_2.258.1084542573                          92       1464120 YES INACTIVE
         1          2 +FRA/mydb/onlinelog/group_2.258.1084542579                           92       1464120 YES INACTIVE
         2          3 +DATA/mydb/onlinelog/group_3.265.1084548477                          25       1464125 YES INACTIVE
         2          3 +FRA/mydb/onlinelog/group_3.259.1084548481                           25       1464125 YES INACTIVE
         2          4 +DATA/mydb/onlinelog/group_4.266.1084548487                          26       1464476 NO  CURRENT
         2          4 +FRA/mydb/onlinelog/group_4.260.1084548489                           26       1464476 NO  CURRENT

8 rows selected.

#*.log_file_name_convert=('+DATA/mydb/onlinelog','/opt/Ora11gData/ora11g')
#*.db_file_name_convert=('+DATA/mydb/datafile','/opt/Ora11gData/ora11g')
#*.db_file_name_convert=('+DATA/mydb/tempfile','/opt/Ora11gData/ora11g')
#*.log_file_name_convert=('+FRA/mydb/onlinelog','/opt/Ora11gData/ora11g')

log_file_name_convert或db_file_name_convert有多条记录不能分开写，因此上面这种写法是错误的，需要写在同一行上，如下所示:
*.log_file_name_convert=('+DATA/mydb/onlinelog','/opt/Ora11gData/ora11g','+FRA/mydb/onlinelog','/opt/Ora11gData/ora11g')
*.db_file_name_convert=('+DATA/mydb/datafile','/opt/Ora11gData/ora11g','+DATA/mydb/tempfile','/opt/Ora11gData/ora11g')





2.RMAN-06025: no backup of archived log for thread 2 
 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 4 was not restored from a sufficiently old backup 
ORA-01110: data file 4: '/opt/Ora11gData/ora11g/undotbs2.263.1084542685'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/28/2021 11:43:05
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 2 with sequence 20 and starting SCN of 1371415 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 89 and starting SCN of 1396907 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 88 and starting SCN of 1371411 found to restore


解决办法:
[oracle@server01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 28 12:11:32 2021

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

connected to target database: MYDB (DBID=2999538401)

RMAN> copy archivelog '+FRA/mydb/archivelog/2021_12_28/thread_1_seq_88.265.1092482977' to '/tmp/rman/thread_1_seq_88.265.1092482977';

Starting backup at 28-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 instance=mydb1 device type=DISK
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=88 RECID=8 STAMP=1092482977
output file name=/tmp/rman/thread_1_seq_88.265.1092482977 RECID=10 STAMP=1092485497
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 28-DEC-21

RMAN> copy archivelog '+FRA/mydb/archivelog/2021_12_28/thread_1_seq_89.263.1092482977' to '/tmp/rman/thread_1_seq_89.263.1092482977';

Starting backup at 28-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 instance=mydb1 device type=DISK
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=89 RECID=9 STAMP=1092482977
output file name=/tmp/rman/thread_1_seq_89.263.1092482977 RECID=11 STAMP=1092485686
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 28-DEC-21

RMAN> copy archivelog '+FRA/mydb/archivelog/2021_12_28/thread_2_seq_20.266.1092482975' to '/tmp/rman/thread_2_seq_20.266.1092482975';

Starting backup at 28-DEC-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=20 RECID=7 STAMP=1092482976
output file name=/tmp/rman/thread_2_seq_20.266.1092482975 RECID=12 STAMP=1092485715
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 28-DEC-21

[oracle@server01 ~]$ scp -r /tmp/rman/thread* 192.168.1.112:/tmp/rman
oracle@192.168.1.112's password: 
thread_1_seq_88.265.1092482977                                                                              100% 3390KB  28.4MB/s   00:00 
thread_1_seq_89.263.1092482977                                                                              100% 1024   483.0KB/s   00:00 
thread_2_seq_20.266.1092482975                                                                              100% 1885KB  23.4MB/s   00:00    
 
[oracle@server ora11g]$ rman target sys/oracle@rac_mydb auxiliary /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 28 12:20:45 2021

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

connected to target database: MYDB (DBID=2999538401)
connected to auxiliary database: ORA11G (not mounted)

RMAN> duplicate target database to ora11g;



3.ORA-01180: can not create datafile 1

RMAN> run{
   allocate channel t1 device type disk;
   allocate channel t2 device type disk;
   set newname for datafile 1 to '/opt/app/oracle/oradata/mydb/system.259.1084542583';
   set newname for datafile 2 to '/opt/app/oracle/oradata/mydb/sysaux.260.1084542629';
   set newname for datafile 3 to '/opt/app/oracle/oradata/mydb/undotbs1.261.1084542659';
   set newname for datafile 4 to '/opt/app/oracle/oradata/mydb/undotbs2.263.1084542685';
   set newname for datafile 5 to '/opt/app/oracle/oradata/mydb/users.264.1084542699';
   set newname for datafile 6 to '/opt/app/oracle/oradata/mydb/test.268.1092418937';
    restore database;
    switch datafile all;
    recover database;
    release channel t1;
    release channel t2;
   }

using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=18 device type=DISK

allocated channel: t2
channel t2: SID=20 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 31-DEC-21

creating datafile file number=1 name=/opt/app/oracle/oradata/mydb/system.259.1084542583
released channel: t1
released channel: t2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/31/2021 17:30:10
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DATA/mydb/datafile/system.259.1084542583'


解决办法:
[oracle@server01 dbs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 31 18:45:44 2021

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

connected to target database: MYDB (DBID=2999538401, not open)

RMAN> catalog start with '/tmp/rman/';