今年2月,客户有个需求把 11g rac环境的数据恢复到单机测试环境,业务准备升级时测试使用。有要求就立刻行动,直接上log不解释
[root@testdb ~]# su - oracle
Last login: Thu Feb 2 21:54:12 CST 2023 on pts/2
orcl:/home/oracle@testdb> cat /u01/orcl.pfile
orcl.__db_cache_size=8086618112
orcl.__java_pool_size=33554432
orcl.__large_pool_size=67108864
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=3221225472
orcl.__sga_target=9663676416
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=1409286144
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/orcl/control01.ctl','/data/orcl/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/data'
*.db_create_online_log_dest_1='/data/orcl'
*.db_create_online_log_dest_2='/data/orcl'
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='LOCATION=/data/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=3221225472
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.sga_target=9663676416
*.undo_tablespace='UNDOTBS1'
orcl:/home/oracle@testdb>mkdir -p /u01/app/oracle/diag/rdbms/orcl/orcl/{incident,incpkg,ir,lck,metadata,metadata_dgif,metadata_pv,sweep,stage,trace}
orcl:/home/oracle@testdb>mkdir -p /u01/app/oracle/admin/orcl/{adump,adump,dpdump,hdump,pfile}
orcl:/home/oracle@testdb>mkdir -p /data/orcl /data/archivelog
orcl:/home/oracle@testdb>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 3 09:39:16 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/u01/orcl.pfile';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 9620525056 bytes
Fixed Size 2261368 bytes
Variable Size 1912606344 bytes
Database Buffers 7683964928 bytes
Redo Buffers 21692416 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
orcl:/home/oracle@testdb>rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 3 09:39:42 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> set dbid=1002255058
executing command: SET DBID
RMAN> restore controlfile from '/data/backup/orcl/c-1002255058-20230127-00';
Starting restore at 2023-02-03 09:40:07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=958 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/orcl/control01.ctl
output file name=/data/orcl/control02.ctl
Finished restore at 2023-02-03 09:40:09
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> crosscheck backup;
..............................
RMAN> delete noprompt EXPIRED backup;
..............................
RMAN> catalog start with '/data/backup/orcl' noprompt;
searching for all files that match the pattern /data/backup/orcl
List of Files Unknown to the Database
=====================================
File Name: /data/backup/orcl/FULLBAK_ORCL_20230127_11874_1
File Name: /data/backup/orcl/FULLBAK_ORCL_20230127_11875_1
File Name: /data/backup/orcl/FULLBAK_ORCL_20230127_11876_1
File Name: /data/backup/orcl/c-1002233058-20230127-00
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230127_11878_1
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230127_11879_1
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230127_11880_1
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230127_11881_1
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230128_11885
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230128_11886
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230128_11887
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230128_11888
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230128_11889
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230129_11891
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230129_11892
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230129_11893
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230129_11894
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230130_11896
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230130_11897
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230130_11898
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230130_11899
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230131_11901
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230131_11902
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230131_11903
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230131_11904
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230131_11905
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230201_11907
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230201_11908
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230201_11909
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230201_11910
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /data/backup/orcl/FULLBAK_ORCL_20230127_11874_1
File Name: /data/backup/orcl/FULLBAK_ORCL_20230127_11875_1
File Name: /data/backup/orcl/FULLBAK_ORCL_20230127_11876_1
File Name: /data/backup/orcl/c-1002233058-20230127-00
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230127_11878_1
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230127_11879_1
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230127_11880_1
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230127_11881_1
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230128_11885
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230128_11886
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230128_11887
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230128_11888
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230128_11889
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230129_11891
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230129_11892
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230129_11893
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230129_11894
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230130_11896
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230130_11897
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230130_11898
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230130_11899
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230131_11901
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230131_11902
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230131_11903
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230131_11904
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230131_11905
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230201_11907
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230201_11908
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230201_11909
File Name: /data/backup/orcl/ARCHBAK_ORCL_20230201_11910
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
set until time "to_date('2023-01-30 08:00:00','yyyy-mm-dd hh24:mi:ss')";
set newname for datafile 1 to '/data/orcl/system.534.856709121';
set newname for datafile 2 to '/data/orcl/sysaux.530.856709121';
set newname for datafile 3 to '/data/jwdb/undotbs1.526.856709121';
set newname for datafile 4 to '/data/orcl/users.522.856709121';
set newname for datafile 5 to '/data/orcl/undotbs2.501.856709317';
set newname for datafile 6 to '/data/orcl/fwpt.dbf';
set newname for datafile 7 to '/data/orcl/tsp_monitor.dbf';
set newname for datafile 8 to '/data/orcl/sysaux.408.973008757';
set newname for datafile 9 to '/data/orcl/eams.376.1053705005';
set newname for tempfile 1 to '/data/orcl/temp.505.856709225';
set newname for tempfile 2 to '/data/orcl/tsp_monitor_temp.dbf';
RESTORE DATABASE;
SWITCH datafile ALL;
SWITCH tempfile ALL;
recover database delete archivelog;
release channel c1;
release channel c2;
}
executing command: SET until clause
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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2023-02-03 09:43:33
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data/orcl/system.534.856709121
channel ORA_DISK_1: restoring datafile 00004 to /data/orcl/users.522.856709121
channel ORA_DISK_1: restoring datafile 00006 to /data/orcl/fwpt.dbf
channel ORA_DISK_1: reading from backup piece /data/backup/orcl/FULLBAK_ORCL_20230127_11876_1
channel ORA_DISK_1: piece handle=/data/backup/orcl/FULLBAK_ORCL_20230127_11876_1 tag=DB_FULL_BAK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /data/orcl/sysaux.530.856709121
channel ORA_DISK_1: restoring datafile 00003 to /data/orcl/undotbs1.526.856709121
channel ORA_DISK_1: restoring datafile 00009 to /data/orcl/eams.376.1053705005
channel ORA_DISK_1: reading from backup piece /data/backup/orcl/FULLBAK_ORCL_20230127_11874_1
channel ORA_DISK_1: piece handle=/data/backup/orcl/FULLBAK_ORCL_20230127_11874_1 tag=DB_FULL_BAK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /data/orcl/undotbs2.501.856709317
channel ORA_DISK_1: restoring datafile 00007 to /data/orcl/tsp_monitor.dbf
channel ORA_DISK_1: restoring datafile 00008 to /data/orcl/sysaux.408.973008757
channel ORA_DISK_1: reading from backup piece /data/backup/orcl/FULLBAK_ORCL_20230127_11875_1
channel ORA_DISK_1: piece handle=/data/backup/orcl/FULLBAK_ORCL_20230127_11875_1 tag=DB_FULL_BAK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 2023-02-03 09:46:49
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1127814409 file name=/data/orcl/system.534.856709121
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=1127814409 file name=/data/orcl/sysaux.530.856709121
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=1127814409 file name=/data/orcl/undotbs1.526.856709121
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=1127814409 file name=/data/orcl/users.522.856709121
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=1127814409 file name=/data/orcl/undotbs2.501.856709317
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=1127814409 file name=/data/orcl/fwpt.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=1127814409 file name=/data/orcl/tsp_monitor.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=17 STAMP=1127814409 file name=/data/orcl/sysaux.408.973008757
datafile 9 switched to datafile copy
input datafile copy RECID=18 STAMP=1127814409 file name=/data/orcl/eams.376.1053705005
renamed tempfile 1 to /data/orcl/temp.505.856709225 in control file
renamed tempfile 2 to /data/orcl/tsp_monitor_temp.dbf in control file
Starting recover at 2023-02-03 09:46:49
using channel ORA_DISK_1
starting media recovery
....................................
channel ORA_DISK_1: reading from backup piece /data/backup/orcl/ARCHBAK_ORCL_20230131_11901
channel ORA_DISK_1: piece handle=/data/backup/orcl/ARCHBAK_ORCL_20230131_11901 tag=ARCH_BAK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/data/archivelog/1_26599_856709220.dbf thread=1 sequence=26599
channel default: deleting archived log(s)
archived log file name=/data/archivelog/2_17966_856709220.dbf RECID=44555 STAMP=1127814514
archived log file name=/data/archivelog/2_17967_856709220.dbf thread=2 sequence=17967
channel default: deleting archived log(s)
archived log file name=/data/archivelog/1_26599_856709220.dbf RECID=44561 STAMP=1127814520
channel default: deleting archived log(s)
archived log file name=/data/archivelog/2_17967_856709220.dbf RECID=44560 STAMP=1127814520
media recovery complete, elapsed time: 00:00:02
Finished recover at 2023-02-03 09:48:42
RMAN> exit
Recovery Manager complete.
orcl:/home/oracle@testdb>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 3 09:57:39 2023
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 rename file '+DATA/orcl/onlinelog/group_1.514.856709221' to '/data/orcl/group_1.514.856709221';
Database altered.
SQL> alter database rename file '+DATA/orcl/onlinelog/group_2.510.856709221' to '/data/orcl/group_2.510.856709221';
Database altered.
SQL> alter database rename file '+DATA/orcl/onlinelog/group_3.497.856709411' to '/data/orcl/group_3.497.856709411';
Database altered.
SQL> alter database rename file '+ARCH/orcl/onlinelog/group_3.469.856709411' to '/data/orcl/group_3.469.856709411';
Database altered.
SQL> alter database rename file '+DATA/orcl/onlinelog/group_4.492.856709411' to '/data/orcl/group_4.492.856709411';
Database altered.
SQL> alter database rename file '+ARCH/orcl/onlinelog/group_4.478.856709413' to '/data/orcl/group_4.478.856709413';
Database altered.
SQL> alter database rename file '+ARCH/orcl/onlinelog/group_1.564.856709221' to '/data/orcl/group_1.564.856709221';
Database altered.
SQL> col MEMBER for a60;
SQL> select GROUP#, member from v$logfile;
GROUP# MEMBER
---------- ------------------------------------------------------------
2 /data/orcl/group_2.510.856709221
2 /data/orcl/group_2.466.856709221
1 /data/orcl/group_1.514.856709221
1 /data/orcl/group_1.564.856709221
3 /data/orcl/group_3.497.856709411
3 /data/orcl/group_3.469.856709411
4 /data/orcl/group_4.492.856709411
4 /data/orcl/group_4.478.856709413
8 rows selected.
SQL> alter database open resetlogs;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
------ ---------------
ORCL READ WRITE
原环境中的数据文件和临时文件、redo文件查看
$sqlplus / as sysdba
set pagesize 199;
set linesize 199;
col name for a50;
select file#,name from v$datafile;
select FILE#,name from v$tempfile;
col MEMBER for a60;
select GROUP#, member from v$logfile;
清除未使用线程的redo日志组,操作如下:
alter database disable thread 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
.。。。。
清除多余的undo文件。
select name from v$tablespace where name like 'UNDO%';
show parameter undo_tablespace;
drop tablespace UNDOTBS2 including contents and datafiles;