我们生产环境中经常采用 rman 自动计划任务或采用备份软件来备份数据库，每天全备份一次或采用增量备份，来防止数据的丢失，然而，实际恢复上产的情况却很少，通常情况下，为了验证数据的可用性，我们会定期在测试机器上来恢复生产上的数据，以此来检查数据文件是否可用，以及与生产系统对比，验证其是否准确。

那么下面我们来测试下 11g 环境下恢复到测试机器上的过程。

1 、操作系统，数据库版本

操作系统 :CentOS Linux release 7.7.1908

数据库 :11.2.0.4.0

rman 恢复到不同的目录改变后：

数据目录： /oradata /oradata/orclnew

归档目录： /archivelog /archivelognew

闪回目录： /falsh_recovery /flash_recoverynew

2 、备份生产库 :

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

backup as compressed backupset tag='full_backup' format '/home/oracle/backup/full_db_%U_%T' database;

sql 'alter system archive log current';

backup archivelog all format '/home/oracle/backup/archivelog_%U_%T' delete all input;

backup current controlfile tag='ctl_file' format='/home/oracle/backup/ctl_file_%U_%T';

backup spfile tag='spfile' format='/home/oracle/backup/spfile_%U_%T';

report obsolete;

delete noprompt obsolete;

crosscheck backup;

delete noprompt expired backup;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

3 、检查生产库参数配置、文件位置、 listener 、 tnsnames 等

原库信息：

表空间：

SQL> /

FILE_ID FILE_NAME TABLESPACE_NAME

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

4 /oradata/orcl/users01.dbf USERS

3 /oradata/orcl/undotbs01.dbf UNDOTBS1

2 /oradata/orcl/sysaux01.dbf SYSAUX

1 /oradata/orcl/system01.dbf SYSTEM

5 /oradata/orcl/example01.dbf EXAMPLE

临时表空间：

SQL> select file_id,file_name,tablespace_name from dba_temp_files;

FILE_ID FILE_NAME TABLESPACE_NAME

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

1 /oradata/orcl/temp01.dbf TEMP

在线日志：

SQL> select member from v$logfile;

MEMBER

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

/oradata/orcl/redo03.log

/oradata/orcl/redo02.log

/oradata/orcl/redo01.log

参数文件：

orcl.__db_cache_size=654311424

orcl.__java_pool_size=16777216

orcl.__large_pool_size=33554432

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=637534208

orcl.__sga_target=956301312

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=234881024

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='/oradata/orcl/control01.ctl','/oradata/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest_size=10737418240

*.db_recovery_file_dest='/flash_recovery'

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

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

*.log_archive_dest_1='location=/archivelog'

*.memory_target=1580204032

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

4 、启动测试库

目标库：

参数文件

[oracle@db1 ~]$ cat 2.sql

orcl.__db_cache_size=654311424

orcl.__java_pool_size=16777216

orcl.__large_pool_size=33554432

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=637534208

orcl.__sga_target=956301312

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=234881024

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='/oradata/orclnew/control01.ctl','/oradata/orclnew/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest_size=10737418240

*.db_recovery_file_dest='/flash_recoverynew'

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

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

*.log_archive_dest_1='location=/archivelognew'

*.memory_target=1580204032

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

[root@db1 ~]# mkdir -p /oradata/orclnew /flash_recoverynew /archivelognew

[root@db1 ~]# chmod 775 /oradata/orclnew /flash_recoverynew /archivelognew -R

[root@db1 ~]# chown oracle:oinstall /oradata/ /flash_recoverynew /archivelognew -R

[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 25 16:35:10 2022

Connected to an idle instance.

SQL> create spfile from pfile='/home/oracle/2.sql';

File created.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1586708480 bytes

Fixed Size 2253624 bytes

Variable Size 989859016 bytes

Database Buffers 587202560 bytes

Redo Buffers 7393280 bytes

5 、恢复测试库

[oracle@db1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 25 16:36:30 2022

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from '/home/oracle/backup/ctl_file_1f0k69vl_1_1_20220125';

Starting restore at 25-JAN-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file

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

output file name=/oradata/orclnew/control01.ctl

output file name=/oradata/orclnew/control02.ctl

Finished restore at 25-JAN-22

[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 25 16:38:00 2022

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.

恢复脚本：

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

set newname for datafile 1 to '/oradata/orclnew/system01.dbf';

set newname for datafile 2 to '/oradata/orclnew/sysaux01.dbf';

set newname for datafile 3 to '/oradata/orclnew/undotbs01.dbf';

set newname for datafile 4 to '/oradata/orclnew/users01.dbf';

set newname for datafile 5 to '/oradata/orclnew/example01.dbf';

restore database;

switch datafile all;

recover database;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 25 16:43:14 2022

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;

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

ORCL READ WRITE

6 、检查库文件 redo ， logfile ，临时文件

SQL> select group#,status,type,member from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

3 ONLINE /oradata/orcl/redo03.log NO

2 ONLINE /oradata/orcl/redo02.log NO

1 ONLINE /oradata/orcl/redo01.log NO

（备注： redo 日志位置需要变更）

SQL> select file_name,tablespace_name from dba_temp_files;

FILE_NAME TABLESPACE_NAME

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

/oradata/orcl/temp01.dbf TEMP

（备注： temp 文件位置未变更，可以迁移，可以新建）

修改 redolog 日志位置：（通过增加成员，删除原来 redo 日志）

alter database add logfile member '/oradata/orclnew/redo01a.log' to group 1;

alter database add logfile member '/oradata/orclnew/redo02a.log' to group 2;

alter database add logfile member '/oradata/orclnew/redo03a.log' to group 3;

删除日志组成员（删除日志组为 INACTIVE 状态）：

SQL> select group#,thread#,bytes/1024/1024 mb,status from v$log;

GROUP# THREAD# MB STATUS

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

1 1 50 CURRENT

2 1 50 INACTIVE

3 1 50 INACTIVE

alter database drop logfile member '/oradata/orcl/redo01.log';

alter database drop logfile member '/oradata/orcl/redo02.log' ;

alter database drop logfile member '/oradata/orcl/redo03.log' ;

SQL> select group#,thread#,bytes/1024/1024 mb,members,status from v$log;

GROUP# THREAD# MB MEMBERS STATUS

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

1 1 50 1 INACTIVE

2 1 50 1 INACTIVE

3 1 50 1 CURRENT

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE /oradata/orclnew/redo01a.log NO

2 ONLINE /oradata/orclnew/redo02a.log NO

3 ONLINE /oradata/orclnew/redo03a.log NO

修改默认临时文件（重新创建文件）

SQL> create temporary tablespace temp1 tempfile '/oradata/orclnew/temp1.dbf' size 200m autoextend on maxsize 2g;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp;

Tablespace dropped.

SQL> select file_name,tablespace_name from dba_temp_files;

FILE_NAME TABLESPACE_NAME

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

/oradata/orclnew/temp1.dbf TEMP1

总结：通过上面恢复过程，恢复过程中可以根据测试环境情况自定义目录，恢复完成后，需要重建 redo 日志， tempfile

文件，检查系统各项参数设置是否设置正常，数据库异机就恢复完成了，也可以用于基于时间和 scn 的不完整恢复， rman 是

日常数据恢复的好帮手。

Yicheng16
22.01.24

-- The End --

ITPub博客

【11g 库异地恢复】实验

最新文章