我們生產環境中經常採用 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 --

【11g 庫異地恢復】實驗

相關文章