OCP学习——数据库整库备份恢复实验

数据库版本信息：Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.7.0.0.0

一、源数据库进行备份

① 查询数据库ID信息（在恢复过程中需要保证ID号相同）

[oracle@test oradata]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 22 10:17:36 2021
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> select dbid from v$database;

     DBID
----------
2363318542

② 查询数据文件的ID和路径信息

SQL> select file_id,file_name from dba_data_files;

  FILE_ID FILE_NAME
---------- --------------------------------------------------
    7 /u01/app/oracle/oradata/TEST/users01.dbf
    4 /u01/app/oracle/oradata/TEST/undotbs01.dbf
    1 /u01/app/oracle/oradata/TEST/system01.dbf
    3 /u01/app/oracle/oradata/TEST/sysaux01.dbf
    5 /u01/app/oracle/oradata/TEST/tbs_test01.dbf

③ 查询重做日志文件的路径信息

SQL> select GROUP#,member from v$logfile;

   GROUP# MEMBER
---------- --------------------------------------------------
    3 /u01/app/oracle/oradata/TEST/redo03.log
    2 /u01/app/oracle/oradata/TEST/redo02.log
    1 /u01/app/oracle/oradata/TEST/redo01.log

④ 查询控制文件的路径信息

SQL> show parameter control_files;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_files		     string	 /u01/app/oracle/oradata/TEST/control01.ctl, /u01/app/oracle/fast_recovery_area/TEST/control02.ctl

⑤ 设置控制文件的自动备份

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;  //修改备份保留周期
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabk/ctrl_%F';   //修改控制文件备份名称
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.3/db_1/dbs/snapcf_test.f'; # default

⑥ RMAN中对数据库进行全备（0级或full备份）

RMAN> backup incremental level 0 database format '/orabk/db_0_%T_%U';

Starting backup at 22-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/TEST/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/TEST/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/TEST/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/TEST/tbs_test01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/TEST/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-FEB-21
channel ORA_DISK_1: finished piece 1 at 22-FEB-21
piece handle=/orabk/db_0_20210222_05vnqpqf_1_1 tag=TAG20210222T121055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 22-FEB-21

Starting Control File and SPFILE Autobackup at 22-FEB-21
piece handle=/orabk/ctrl_c-2363318542-20210222-02 comment=NONE
Finished Control File and SPFILE Autobackup at 22-FEB-21

⑦ 查看数据空归档日志路径，切换归档日志文件，使所有数据库修改都写入了归档日志中

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence	       9

SQL> show parameter db_recovery_file_dest

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size	     big integer 8256M

⑧ 确定最后的SCN号

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   2178451

⑨ 将⑤⑥⑦步骤产生的结果拷贝到新系统中

二、目标数据库进行恢复

2.1 目标数据库目录层次与源数据库目录层次均一致

① 设置环境变量，保持与源环境一致
② 创建空实例（仅windows系统需要）
③ 启动数据库导nomount状态

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.3/db_1/dbs/inittest.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1073737800 bytes

Fixed Size                     8904776 bytes
Variable Size                285212672 bytes
Database Buffers             771751936 bytes
Redo Buffers                   7868416 bytes

④ 设置数据库DBID

RMAN> set dbid 2363318542;

executing command: SET DBID

⑤ 设置控制文件自动备份的路径

RMAN> set controlfile autobackup format for  device type disk to '/orabk/ctrl_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

⑥ 恢复参数文件，重启数据库至nomount状态

RMAN> restore spfile from autobackup;

Starting restore at 25-FEB-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210225
channel ORA_DISK_1: AUTOBACKUP found: /orabk/ctrl_c-2363318542-20210225-01
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /orabk/ctrl_c-2363318542-20210225-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 25-FEB-21

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area   10099882168 bytes

Fixed Size                    12445880 bytes
Variable Size               1543503872 bytes
Database Buffers            8522825728 bytes
Redo Buffers                  21106688 bytes

⑦ 恢复控制文件，切换实例至mount状态

RMAN> restore controlfile from autobackup;

Starting restore at 23-FEB-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=329 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: TEST
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST/autobackup/2021_02_22/o1_mf_s_1065174392_j3637rwv_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST/autobackup/2021_02_22/o1_mf_s_1065174392_j3637rwv_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/TEST/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/TEST/control02.ctl
Finished restore at 23-FEB-21

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

⑧ 恢复数据文件（指定备份文件的路径信息）

RMAN> run {
2> set until scn 2178451;
3> catalog start with '/orabk';
4> restore database;
5> recover database;
6> }

⑨ 打开数据库

RMAN> alter database open resetlogs;

Statement processed

2.2 目标数据库目录层次与源数据库目录层次均不一致

① 设置环境变量，保持与源环境一致
② 创建空实例（仅windows系统需要）
③ 启动数据库导nomount状态

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.3/db_1/dbs/inittest.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1073737800 bytes

Fixed Size                     8904776 bytes
Variable Size                285212672 bytes
Database Buffers             771751936 bytes
Redo Buffers                   7868416 bytes

④ 设置数据库DBID

RMAN> set dbid 2363318542;

executing command: SET DBID

⑤ 设置控制文件自动备份的路径（若为开启控制文件自动备份，该步骤其实无效）

RMAN> set controlfile autobackup format for  device type disk to '/orabk/ctrl_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

⑥ 恢复参数文件为文本文件，并修改相关变动的参数值后，重新转化为二进制文件

//还原参数文件，同时将参数文件转化为文本文件
RMAN> restore spfile to pfile '/tmp/tmp.ora'  from autobackup;

Starting restore at 25-FEB-21
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /orabk/ctrl_c-2363318542-20210222-02
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 25-FEB-21

//完成参数文件修改后，将文本文件转化为二进制参数文件
RMAN> create spfile from pfile='/tmp/tmp.ora';

using target database control file instead of recovery catalog
Statement processed

⑦ 使用修改后的参数文件重新启动数据库至nomount状态

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area   10099882168 bytes

Fixed Size                    12445880 bytes
Variable Size               1543503872 bytes
Database Buffers            8522825728 bytes
Redo Buffers                  21106688 bytes

⑧ 设置dbid与控制文件备份路径（步骤④⑤），恢复控制文件，启动数据库至mount状态

RMAN> restore controlfile from autobackup;

Starting restore at 25-FEB-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=656 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: TEST
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210225
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210224
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210223
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210222
channel ORA_DISK_1: AUTOBACKUP found: /orabk/ctrl_c-2363318542-20210222-02
channel ORA_DISK_1: restoring control file from AUTOBACKUP /orabk/ctrl_c-2363318542-20210222-02
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/oradata/TEST/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/TEST/control02.ctl
Finished restore at 25-FEB-21

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

⑨ 恢复数据文件

RMAN>run {
set newname for datafile 1 to '/oradata/TEST/system01.dbf';
set newname for datafile 3 to '/oradata/TEST/sysaux01.dbf';
set newname for datafile 4 to '/oradata/TEST/undotbs01.dbf';
set newname for datafile 5 to '/oradata/TEST/tbs_test01.dbf';
set newname for datafile 7 to '/oradata/TEST/users01.dbf';
SQL "alter database rename file ''/u01/app/oracle/oradata/TEST/redo03.log'' to ''/oradata/TEST/redo03.log''";
SQL "alter database rename file ''/u01/app/oracle/oradata/TEST/redo02.log'' to ''/oradata/TEST/redo02.log''";
SQL "alter database rename file ''/u01/app/oracle/oradata/TEST/redo01.log'' to ''/oradata/TEST/redo01.log''";
set until scn 2451649;
catalog start with '/orabk';
restore database;
switch datafile all;
recover database;
}

⑩ 打开数据库

RMAN> alter database open resetlogs;

Statement processed

三、总结

通过上述步骤可以正常打开数据库，但是实际过程中还出现了许多报错情况，尤其是在数据库文件路径发生变化的情况下，总体恢复过程都比较顺利，只是有两个问题尚未解决：

1. 在重新配置重做日志文件路径时，提示了如下错误：

RMAN> alter database rename file '/u01/app/oracle/oradata/TEST/redo03.log' to '/oradata/TEST/redo03.log';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 02/26/2021 08:32:02
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file "/u01/app/oracle/oradata/TEST/redo03.log" in the current container

2. 总是无法恢复与源数据库一致的scn号，明明已经将所有的归档日志拷贝过来了，但是这个问题好像并不影响数据库恢复，恢复的数据库已经是最新的状态了。

以上问题记录一下，若哪位大佬知道或碰到过类似的问题，麻烦告诉我一下原因，不慎感激，若文章内容存在任何问题，也感谢您的指导和交流。