环境：
数据库版本：10201
1.源端数据库：oracle 10g RAC ASM datbase
2.目标数据库：oracle 10g single datbase
1参数文件的恢复
1.1.恢复参数文件
[oracle@ora10g ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Mar 11 00:17:30 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/oracle/product/10.2/dbs/initoradb.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 1218244 bytes
Variable Size 58722620 bytes
Database Buffers 92274688 bytes
Redo Buffers 7168000 bytes
RMAN> restore spfile to pfile "/u01/oracle/yd.ora" from "/u01/rmanbak/c-2562551067-20130310-00";
Starting restore at 11-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /u01/rmanbak/c-2562551067-20130310-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 11-MAR-13
RMAN>
1.2.修改参数文件
more yd.ora
*.audit_file_dest='/u01/oracle/admin/oradb/adump'
*.background_dump_dest='/u01/oracle/admin/oradb/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/oradb/control01.ctl','/u01/oracle/oradata/oradb/control02.ctl','/u01/oracle/o
radata/oradb/control03.ctl'
*.core_dump_dest='/u01/oracle/admin/oradb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='oradb'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=54371840
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=185212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/oradb/udump'
1.3.创建相关目录
[oracle@ora10g admin]$ mkdir -p /u01/oracle/admin/oradb/udump
[oracle@ora10g admin]$ mkdir -p /u01/oracle/admin/oradb/cdump
[oracle@ora10g admin]$ mkdir -p /u01/oracle/admin/oradb/bdump
[oracle@ora10g admin]$ mkdir -p /u01/oracle/admin/oradb/adump
[oracle@ora10g admin]$ mkdir -p /u01/oracle/admin/oradb/pfile
[oracle@ora10g admin]$ mkdir -p /u01/oracle/oradb
1.4.创建spfile文件
[oracle@ora10g oracle]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 11 00:25:10 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create spfile from pfile='/u01/oracle/yd.ora';
File created.
SQL>
SQL> create pfile from spfile;
File created.
SQL>
2.恢复控制文件
2.1.从备份集中恢复controlfile
[oracle@ora10g oracle]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 11 00:26:12 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 1218412 bytes
Variable Size 62916756 bytes
Database Buffers 117440512 bytes
Redo Buffers 7168000 bytes
SQL>
SQL>
oracle@ora10g ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Mar 11 00:26:36 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: oradb (not mounted)
RMAN> set dbid=2562551067
executing command: SET DBID
RMAN>
restore controlfile from '/u01/rmanbak/c-2562551067-20130310-00';
RMAN> restore controlfile from '/u01/rmanbak/c-2562551067-20130310-00';
Starting restore at 11-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/oracle/oradata/oradb/control01.ctl
output filename=/u01/oracle/oradata/oradb/control02.ctl
output filename=/u01/oracle/oradata/oradb/control03.ctl
Finished restore at 11-MAR-13
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
2.2.注册rman备份集到控制文件
catalog start with '/u01/rmanbak';
RMAN> catalog start with '/u01/rmanbak';
searching for all files that match the pattern /u01/rmanbak
List of Files Unknown to the Database
=====================================
File Name: /u01/rmanbak/full_05o46e43_ORADB_20130310
File Name: /u01/rmanbak/arch_03o46e3u_ORADB_20130310
File Name: /u01/rmanbak/arch_08o46e50_ORADB_20130310
File Name: /u01/rmanbak/c-2562551067-20130310-00
File Name: /u01/rmanbak/full_06o46e4i_ORADB_20130310
File Name: /u01/rmanbak/full_04o46e44_ORADB_20130310
File Name: /u01/rmanbak/yd.ora
File Name: /u01/rmanbak/arch_07o46e51_ORADB_20130310
File Name: /u01/rmanbak/arch_02o46e3s_ORADB_20130310
File Name: /u01/rmanbak/arch_01o46e3n_ORADB_20130310
File Name: /u01/rmanbak/rman2.tar
File Name: /u01/rmanbak/rman1.tar
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/rmanbak/full_05o46e43_ORADB_20130310
File Name: /u01/rmanbak/arch_03o46e3u_ORADB_20130310
File Name: /u01/rmanbak/arch_08o46e50_ORADB_20130310
File Name: /u01/rmanbak/c-2562551067-20130310-00
File Name: /u01/rmanbak/full_06o46e4i_ORADB_20130310
File Name: /u01/rmanbak/full_04o46e44_ORADB_20130310
File Name: /u01/rmanbak/arch_07o46e51_ORADB_20130310
File Name: /u01/rmanbak/arch_02o46e3s_ORADB_20130310
File Name: /u01/rmanbak/arch_01o46e3n_ORADB_20130310
List of Files Which Where Not Cataloged
=======================================
File Name: /u01/rmanbak/yd.ora
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/rmanbak/rman2.tar
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/rmanbak/rman1.tar
RMAN-07517: Reason: The file header is corrupted
ist of Backup Sets
===================
。。。。。
3.查看控制文件内容
set line 100
col NAME for a80
col MEMBER for a80
select file#,status,name from v$datafile
union all
select group#,status,member from v$logfile
union all
select file#,status,name from v$tempfile;
SQL> set line 100
SQL> col NAME for a80
SQL> col MEMBER for a80
SQL> select file#,status,name from v$datafile
2 union all
3 select group#,status,member from v$logfile
4 union all
5 select file#,status,name from v$tempfile;
FILE# STATUS NAME
---------- ------- --------------------------------------------------------------------------------
1 SYSTEM +ASMDG/oradb/datafile/system.256.809286221
2 ONLINE +ASMDG/oradb/datafile/undotbs1.258.809286223
3 ONLINE +ASMDG/oradb/datafile/sysaux.257.809286223
4 ONLINE +ASMDG/oradb/datafile/users.259.809286223
5 ONLINE +ASMDG/oradb/datafile/undotbs2.264.809286357
6 ONLINE +ASMDG/oradb/datafile/ts1.268.809708305
7 ONLINE +ASMDG/oradb/datafile/yd.269.809708359
2 +ASMDG/oradb/onlinelog/group_2.262.809286305
1 +ASMDG/oradb/onlinelog/group_1.261.809286303
3 +ASMDG/oradb/onlinelog/group_3.265.809286383
4 +ASMDG/oradb/onlinelog/group_4.266.809286385
1 ONLINE +ASMDG/oradb/tempfile/temp.263.809286315
12 rows selected.
4.数据库恢复
4.1.restore数据文件和临时数据文件
run{
set newname for datafile 1 to '/u01/oracle/oradata/oradb/system01.dbf';
set newname for datafile 2 to '/u01/oracle/oradata/oradb/undotbs1.dbf';
set newname for datafile 3 to '/u01/oracle/oradata/oradb/sysaux01.dbf';
set newname for datafile 4 to '/u01/oracle/oradata/oradb/users01.dbf';
set newname for datafile 5 to '/u01/oracle/oradata/oradb/undotbs02.dbf';
set newname for datafile 6 to '/u01/oracle/oradata/oradb/ts1.dbf';
set newname for datafile 7 to '/u01/oracle/oradata/oradb/yd.dbf';
set newname for tempfile 1 to '/u01/oracle/oradata/oradb/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
}
RMAN>
RMAN> run{
2> set newname for datafile 1 to '/u01/oracle/oradata/oradb/system01.dbf';
3> set newname for datafile 2 to '/u01/oracle/oradata/oradb/undotbs1.dbf';
4> set newname for datafile 3 to '/u01/oracle/oradata/oradb/sysaux01.dbf';
5> set newname for datafile 4 to '/u01/oracle/oradata/oradb/users01.dbf';
6> set newname for datafile 5 to '/u01/oracle/oradata/oradb/undotbs02.dbf';
7> set newname for datafile 6 to '/u01/oracle/oradata/oradb/ts1.dbf';
8> set newname for datafile 7 to '/u01/oracle/oradata/oradb/yd.dbf';
9> set newname for tempfile 1 to '/u01/oracle/oradata/oradb/temp01.dbf';
10> restore database;
11> switch datafile all;
12> switch tempfile all;
13> }
executing command: SET NEWNAME
executing command: SET NEWNAME
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 11-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/oracle/oradata/oradb/undotbs1.dbf
restoring datafile 00003 to /u01/oracle/oradata/oradb/sysaux01.dbf
restoring datafile 00006 to /u01/oracle/oradata/oradb/ts1.dbf
restoring datafile 00007 to /u01/oracle/oradata/oradb/yd.dbf
channel ORA_DISK_1: reading from backup piece /oracle/rmanbak/full_05o46e43_ORADB_20130310
channel ORA_DISK_1: restored backup piece 1
failover to piece handle=/u01/rmanbak/full_05o46e43_ORADB_20130310 tag=TAG20130310T155547
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oracle/oradata/oradb/system01.dbf
restoring datafile 00004 to /u01/oracle/oradata/oradb/users01.dbf
restoring datafile 00005 to /u01/oracle/oradata/oradb/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /oracle/rmanbak/full_04o46e44_ORADB_20130310
channel ORA_DISK_1: restored backup piece 1
failover to piece handle=/u01/rmanbak/full_04o46e44_ORADB_20130310 tag=TAG20130310T155547
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 11-MAR-13
datafile 1 switched to datafile copy
input datafile copy recid=8 stamp=809742972 filename=/u01/oracle/oradata/oradb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=9 stamp=809742973 filename=/u01/oracle/oradata/oradb/undotbs1.dbf
datafile 3 switched to datafile copy
input datafile copy recid=10 stamp=809742973 filename=/u01/oracle/oradata/oradb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=11 stamp=809742973 filename=/u01/oracle/oradata/oradb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=12 stamp=809742973 filename=/u01/oracle/oradata/oradb/undotbs02.dbf
datafile 6 switched to datafile copy
input datafile copy recid=13 stamp=809742973 filename=/u01/oracle/oradata/oradb/ts1.dbf
datafile 7 switched to datafile copy
input datafile copy recid=14 stamp=809742973 filename=/u01/oracle/oradata/oradb/yd.dbf
renamed temporary file 1 to /u01/oracle/oradata/oradb/temp01.dbf in control file
RMAN>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/oradb/system01.dbf
/u01/oracle/oradata/oradb/undotbs1.dbf
/u01/oracle/oradata/oradb/sysaux01.dbf
/u01/oracle/oradata/oradb/users01.dbf
/u01/oracle/oradata/oradb/undotbs02.dbf
/u01/oracle/oradata/oradb/ts1.dbf
/u01/oracle/oradata/oradb/yd.dbf
7 rows selected.
SQL>
SQL>
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/oradb/temp01.dbf
SQL>
4.2.修改redo file的文件名
SQL> select GROUP#,MEMBER from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
2 +ASMDG/oradb/onlinelog/group_2.262.809286305
1 +ASMDG/oradb/onlinelog/group_1.261.809286303
3 +ASMDG/oradb/onlinelog/group_3.265.809286383
4 +ASMDG/oradb/onlinelog/group_4.266.809286385
SQL> alter database rename file '+ASMDG/oradb/onlinelog/group_1.261.809286303' to
'/u01/oracle/oradata/oradb/redo01.log';
Database altered.
SQL> alter database rename file '+ASMDG/oradb/onlinelog/group_2.262.809286305' to
'/u01/oracle/oradata/oradb/redo02.log';
Database altered.
SQL> alter database rename file '+ASMDG/oradb/onlinelog/group_3.265.809286383' to
'/u01/oracle/oradata/oradb/redo03.log';
Database altered.
SQL> alter database rename file '+ASMDG/oradb/onlinelog/group_4.266.809286385' to
'/u01/oracle/oradata/oradb/redo04.log';
Database altered.
4.3. recover数据库
RMAN> recover database;
Starting recover at 11-MAR-13
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=10
channel ORA_DISK_1: reading from backup piece /oracle/rmanbak/arch_07o46e51_ORADB_20130310
channel ORA_DISK_1: restored backup piece 1
failover to piece handle=/u01/rmanbak/arch_07o46e51_ORADB_20130310 tag=TAG20130310T155616
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/oracle/product/10.2/dbs/arch1_10_809286303.dbf thread=1 sequence=10
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=9
channel ORA_DISK_1: reading from backup piece /oracle/rmanbak/arch_08o46e50_ORADB_20130310
channel ORA_DISK_1: restored backup piece 1
failover to piece handle=/u01/rmanbak/arch_08o46e50_ORADB_20130310 tag=TAG20130310T155616
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/oracle/product/10.2/dbs/arch2_9_809286303.dbf thread=2 sequence=9
unable to find archive log
archive log thread=1 sequence=11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/11/2013 00:39:45
RMAN-06054: media recovery requesting unknown log: thread 1 seq 11 lowscn 509191
RMAN>
set line 1000
select group#,thread#,sequence#,archived,status from v$log;
SQL> set line 1000
SQL> select group#,thread#,sequence#,archived,status from v$log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 10 YES INACTIVE
2 1 11 NO CURRENT
3 2 9 YES INACTIVE
4 2 10 NO CURRENT
4.4.
使用resetlogs打开数据库
RMAN> alter database open resetlogs;
database opened
RMAN>
5.修正temp文件
SQL> col PROPERTY_NAME for a30
SQL> col DESCRIPTION for a50
SQL> col PROPERTY_VALUE for a20
SQL> select * from database_properties where property_value='TEMP';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
SQL> select * from database_properties where property_value='USERS';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------- --------------------------------------------------
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
col FILE_NAME for a40
select tablespace_name,file_name,bytes/1024/1024 sizeM,AUTOEXTENSIBLE from dba_temp_files;
SQL> select tablespace_name,file_name,bytes/1024/1024 sizeM,AUTOEXTENSIBLE from dba_temp_files;
TABLESPACE_NAME FILE_NAME SIZEM AUT
------------------------------ ---------------------------------------- ---------- ---
TEMP /u01/oracle/oradata/oradb/temp01.dbf 20 YES
6.修正redo日志
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PUBLIC
SQL>
SQL>
SQL> alter database disable thread 2;
Database altered.
SQL>
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED DISABLED
SQL> select group#,thread#,sequence#,archived,status from v$log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 0 YES UNUSED
2 1 1 NO CURRENT
3 2 0 YES UNUSED
4 2 1 NO INACTIVE
SQL>
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance oradb2 (thread 2) needs to be archived
ORA-00312: online log 4 thread 2: '/u01/oracle/oradata/oradb/redo04.log'
SQL> alter database clear unarchived logfile group 4;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database add logfile group 3 '/u01/oracle/oradata/oradb/redo03.log' size 50m;
Database altered.
SQL> set linesize 300
SQL> l
1 SELECT v$logfile.member, v$logfile.group#, 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#
SQL> /
MEMBER GROUP# STATUS ARC V$LOG.BYTES/1024/1024 THREAD#
---------------------------------------- ---------- ---------------- --- --------------------- ----------
/u01/oracle/oradata/oradb/redo01.log 1 UNUSED YES 50 1
/u01/oracle/oradata/oradb/redo02.log 2 CURRENT NO 50 1
/u01/oracle/oradata/oradb/redo03.log 3 UNUSED NO 50 1
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/product/10.2/dbs/arch
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL>
SQL>
SQL> alter system set log_archive_dest_1='location=/u01/oracle/archivelog';
System altered.
切换归档验证redo日志文件的可用性。
7.修正undo
SQL>
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
SQL>
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL>
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME
------------------------------
UNDOTBS1
8.验证
SQL> col name format a50
SQL> select file#,status,fuzzy,checkpoint_change#,name from v$datafile_header
FILE# STATUS FUZ CHECKPOINT_CHANGE# NAME
---------- ------- --- ------------------ --------------------------------------------------
1 ONLINE YES 509682 /u01/oracle/oradata/oradb/system01.dbf
2 ONLINE YES 509682 /u01/oracle/oradata/oradb/undotbs1.dbf
3 ONLINE YES 509682 /u01/oracle/oradata/oradb/sysaux01.dbf
4 ONLINE YES 509682 /u01/oracle/oradata/oradb/users01.dbf
6 ONLINE YES 509682 /u01/oracle/oradata/oradb/ts1.dbf
7 ONLINE YES 509682 /u01/oracle/oradata/oradb/yd.dbf
6 rows selected.
SQL> select file#,status,checkpoint_change#,last_change#,name from v$datafile;
FILE# STATUS CHECKPOINT_CHANGE# LAST_CHANGE# NAME
---------- ------- ------------------ ------------ --------------------------------------------------
1 SYSTEM 509682 /u01/oracle/oradata/oradb/system01.dbf
2 ONLINE 509682 /u01/oracle/oradata/oradb/undotbs1.dbf
3 ONLINE 509682 /u01/oracle/oradata/oradb/sysaux01.dbf
4 ONLINE 509682 /u01/oracle/oradata/oradb/users01.dbf
6 ONLINE 509682 /u01/oracle/oradata/oradb/ts1.dbf
7 ONLINE 509682 /u01/oracle/oradata/oradb/yd.dbf
6 rows selected.
SQL> conn test/test
Connected.
SQL>
SQL> select count(*) from test;
COUNT(*)
----------
49781
SQL> conn yd/yd
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
14