Oracle 备份与恢复测试

103 阅读 0 评论 68 点赞

我是靠谱客的博主雪白电脑，这篇文章主要介绍Oracle 备份与恢复测试，现在分享给大家，希望可以做个参考。

一、oracle 11gR2

1.单实例的备份与恢复

2.RAC集群的备份与恢复

3.DG的备份与恢复

二、oracle 19c

1.单实例的备份与恢复

2.RAC集群的备份与恢复

3.DG的备份与恢复

一、oracle 11gR2

1.单实例的备份与恢复

1）使用rman 备份spfile

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[oracle@oracle11 admin]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Mar 21 17:03:41 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1593839605)

RMAN> backup spfile;

Starting backup at 2021-03-21 17:03:49
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=360 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2021-03-21 17:03:51
channel ORA_DISK_1: finished piece 1 at 2021-03-21 17:03:52
piece handle=/data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_nnsnf_TAG20210321T170351_j5g2zqxh_.bkp tag=TAG20210321T170351 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-21 17:03:53

RMAN>

2)误删除spfile,然后想备份pfile

复制代码

1
2
3
4
5
6
7
8
9
10
SQL> create pfile='/tmp/17.ora' from spfile;
create pfile='/tmp/17.ora' from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file
'/data/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

但是可以从内存重建pfile（但这得及时发现才行，不然重启数据库后就麻烦了）

复制代码

1
2
3
4
5
6
SQL> create pfile='/tmp/17.ora' from memory;

File created.

SQL>

关闭数据库，启动成功0 0哈哈，原为我默认位置还有Initorcl.ora的第二启动序号的参数文件0 0，都删除掉，终于报错了，试个错都不容易

复制代码

1
2
3
4
5
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/data/oracle/product/11.2.0/db_1/dbs/initorcl.ora'
SQL>

2）使用rman 恢复spfile

2.1) startup nomount 状态，提示找不到initorcl.ora

2.2)

复制代码

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/data/oracle/product/11.2.0/db_1/dbs/initorcl.ora'

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

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                281019272 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5517312 bytes

RMAN> restore spfile to '/data/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora' from '/data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_nnsnf_TAG20210321T170351_j5g2zqxh_.bkp';

Starting restore at 2021-03-21 17:34:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_nnsnf_TAG20210321T170351_j5g2zqxh_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2021-03-21 17:34:24

RMAN>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/data/oracle/product/11.2.0/db_1/dbs/initorcl.ora'

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

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                281019272 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5517312 bytes

RMAN> restore spfile to '/data/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora' from '/data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_nnsnf_TAG20210321T170351_j5g2zqxh_.bkp';

Starting restore at 2021-03-21 17:34:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_nnsnf_TAG20210321T170351_j5g2zqxh_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2021-03-21 17:34:24

RMAN>

恢复成功

[oracle@oracle11 dbs]$ ls -lrt
total 9584
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Mar 1 15:27 lkORCL
-rw-r----- 1 oracle oinstall 2560 Mar 3 10:57 spfileorcl.ora.bak
-rw-r--r-- 1 oracle oinstall 955 Mar 3 11:18 pfile.ora
-rw-r----- 1 oracle oinstall 1536 Mar 21 13:56 orapworcl
-rw-r----- 1 oracle oinstall 9781248 Mar 21 17:03 snapcf_orcl.f
-rw-r----- 1 oracle oinstall 3584 Mar 21 17:34 spfileorcl.ora
-rw-r----- 1 oracle oinstall 24 Mar 21 17:42 lkDUMMY
-rw-rw---- 1 oracle oinstall 1544 Mar 21 17:42 hc_orcl.dat
[oracle@oracle11 dbs]$

rman关闭数据库：

RMAN> shutdown immediate

using target database control file instead of recovery catalog
Oracle instance shut down

RMAN>

sqlplus启动数据库

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
SQL> startup
ORACLE instance started.

Total System Global Area 1670221824 bytes
Fixed Size                  2253824 bytes
Variable Size            1375734784 bytes
Database Buffers          285212672 bytes
Redo Buffers                7020544 bytes
Database mounted.
Database opened.
SQL>

查看备份信息（平时不知道位置怎么办0 0！急，这个命令是mount才能查看的）：

RMAN> list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1 Full 80.00K DISK 00:00:01 2021-03-21 17:03:52
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20210321T170351
Piece Name: /data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_nnsnf_TAG20210321T170351_j5g2zqxh_.bkp
SPFILE Included: Modification time: 2021-03-21 16:49:14
SPFILE db_unique_name: ORCL

RMAN>

不指定备份文件恢复spfile,默认使用最新的备份。先改个sga的参数，然后做备份测试

restore spfile to '/tmp/spfile20210321.ora';

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
RMAN> restore spfile to '/tmp/spfile20210321.ora';

Starting restore at 2021-03-21 17:53:42
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=/tmp/spfile20210321.ora
channel ORA_DISK_1: reading from backup piece /data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_nnsnf_TAG20210321T170351_j5g2zqxh_.bkp
channel ORA_DISK_1: piece handle=/data/oracle/fast_recovery_area/ORCL/backupset/2021_03_21/o1_mf_nnsnf_TAG20210321T170351_j5g2zqxh_.bkp tag=TAG20210321T170351
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2021-03-21 17:53:43

RMAN>