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.
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/';
七、恢复后处理工作
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.重建临时表空间