Oracle 19c RAC异机恢复到单机19c(包含PDB)

前言
本文介绍Oracle 19c RAC数据库异机恢复到单机19c数据库，包含PDB可插拔数据库。

本实验适用的场景包括：
数据库恢复演练
数据库宕机后的数据恢复
数据误删除后的异机恢复指定的数据
测试环境克隆等。

下面开始实验操作

一、目标端准备环境

1.1、环境准备

安装操作系统，安装数据库软件，不建库。在此不演示了。请小伙伴们自行查找资料准备环境吧。

1.2、创建相关目录

su - oracle
mkdir -p /u01/app/oracle/admin/prodcdb/adump
mkdir -p /oradata/prodcdb/pdbseed
mkdir -p /oradata/prodcdb/pdbprod1
mkdir -p /oradata/prodcdb/pdbprod2
mkdir -p /home/oracle/archivelog
mkdir -p /home/oracle/rmanbk
1
2
3
4
5
6
7

二、源端准备要恢复的数据和配置

2.1、rac数据库进行rman全备

rman target /
backup database format '/home/oracle/rmanbk/db_%U.bak' plus archivelog  format  '/home/oracle/rmanbk/arch_%U.bak';

backup current controlfile format '/home/oracle/rmanbk/control.bak';
1
2
3
4

2.2、将备份的数据文件、归档文件、控制文件 scp拷贝到目的端。

2.3、准备pfile文件

sqlplus / as sysdba
create pfile=/home/oracle/pfile.ora from spfile;
1
2

2.4、修改pfile文件并scp拷贝到目的端

[oracle@cnrac1 ~]$ cat pfile.ora
*.audit_file_dest='/u01/app/oracle/admin/prodcdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='/oradata/prodcdb/current.311.1059256243'
*.db_block_size=8192
*.db_name='prodcdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodcdbXDB)'
*.enable_pluggable_database=true
*.log_archive_dest_1='location=/home/oracle/archivelog'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=780m
*.processes=640
*.remote_login_passwordfile='exclusive'
*.sga_target=2340m
[oracle@cnrac1 ~]$
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

三、目的端恢复数据库

3.1、准备pifle文件：同上

sqlplus  /  as sysdba
create spfile from pfile;
startup nomount
1
2
3

3.2、使用rman恢复控制文件

rman target /
restore controlfile from '/home/oracle/rmanbk/c-3057330226-20201222-01';
1
2

3.3、启动到mount

alter database mount;
1

3.4、注册新增加的归档日志文件

catalog start  with '/home/oracle/archivelog';
1

3.5、查看备份的数据文件

list backup;
1

3.6、查看备份的归档日志文件

list backup of archivelog all;
1

3.7、查看注册的新的归档日志文件

list archivelog all;
RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name PRODCDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
6       1    20      A 22-DEC-20
        Name: /home/oracle/archivelog/1_20_1059256246.dbf

7       1    21      A 22-DEC-20
        Name: /home/oracle/archivelog/1_21_1059256246.dbf

8       1    22      A 22-DEC-20
        Name: /home/oracle/archivelog/1_22_1059256246.dbf

9       1    23      A 22-DEC-20
        Name: /home/oracle/archivelog/1_23_1059256246.dbf

10      1    24      A 22-DEC-20
        Name: /home/oracle/archivelog/1_24_1059256246.dbf

11      1    25      A 22-DEC-20
        Name: /home/oracle/archivelog/1_25_1059256246.dbf

12      1    26      A 22-DEC-20
        Name: /home/oracle/archivelog/1_26_1059256246.dbf

13      1    27      A 22-DEC-20
        Name: /home/oracle/archivelog/1_27_1059256246.dbf

14      1    28      A 22-DEC-20
        Name: /home/oracle/archivelog/1_28_1059256246.dbf

15      1    29      A 22-DEC-20
        Name: /home/oracle/archivelog/1_29_1059256246.dbf


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
28
29
30
31
32
33
34
35
36
37
38
39
40

3.8、执行恢复操作

恢复步骤如下：
(1)指定最新的archivelog sequence 号是上面查看到的29号;
(2)将数据文件重命名,根据list backup的文件名进行相应的修改；
(3)还原数据文件；
(4)切换到新的数据文件路径；
(5)恢复数据库；

详细命令如下：

rman target /
run{
set until sequence 29 thread 1;
set NEWNAME for datafile 1 to '/oradata/prodcdb/system01.dbf';
set NEWNAME for datafile 3 to '/oradata/prodcdb/sysaux01.dbf';
set newname for datafile 4 to '/oradata/prodcdb/undotbs01.dbf';
set newname for datafile 7 to '/oradata/prodcdb/users01.dbf';
set newname for datafile 9 to '/oradata/prodcdb/undotbs02.dbf';
set newname for datafile 10 to '/oradata/prodcdb/pdbprod1/system01.dbf';
set newname for datafile 11 to '/oradata/prodcdb/pdbprod1/sysaux01.dbf';
set newname for datafile 12 to '/oradata/prodcdb/pdbprod1/undotbs01.dbf';
set newname for datafile 13 to '/oradata/prodcdb/pdbprod1/users01.dbf';
set newname for datafile 18 to '/oradata/prodcdb/pdbprod1/test01.dbf';
set newname for datafile 14 to '/oradata/prodcdb/pdbprod2/system01.dbf';
set newname for datafile 15 to '/oradata/prodcdb/pdbprod2/sysaux01.dbf';
set newname for datafile 16 to '/oradata/prodcdb/pdbprod2/undotbs01.dbf';
set newname for datafile 17 to '/oradata/prodcdb/pdbprod2/users01.dbf';
set newname for datafile 5 to '/oradata/prodcdb/pdbseed/system01.dbf';
set newname for datafile 6 to '/oradata/prodcdb/pdbseed/sysaux01.dbf';
set newname for datafile 8 to '/oradata/prodcdb/pdbseed/undotbs01.dbf';
restore database;
switch datafile all;
switch datafile all;
recover database;
}
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

3.9、查看日志文件并重命名到新的路径

SYS@prodcdb>select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/PRODCDB/ONLINELOG/group_2.313.1059256247
+DATA/PRODCDB/ONLINELOG/group_1.312.1059256247
+DATA/PRODCDB/ONLINELOG/group_3.281.1059257599
+DATA/PRODCDB/ONLINELOG/group_4.288.1059257605
SYS@prodcdb>
1
2
3
4
5
6
7
8

3.10、重命名到新路径

alter database rename file '+DATA/PRODCDB/ONLINELOG/group_1.312.1059256247' to '/oradata/prodcdb/redo01a.log';
alter database rename file '+DATA/PRODCDB/ONLINELOG/group_2.313.1059256247' to '/oradata/prodcdb/redo02a.log';
alter database rename file '+DATA/PRODCDB/ONLINELOG/group_3.281.1059257599' to '/oradata/prodcdb/redo03a.log';
alter database rename file '+DATA/PRODCDB/ONLINELOG/group_4.288.1059257605' to '/oradata/prodcdb/redo04a.log';

SYS@prodcdb>select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/prodcdb/redo02a.log
/oradata/prodcdb/redo01a.log
/oradata/prodcdb/redo03a.log
/oradata/prodcdb/redo04a.log
1
2
3
4
5
6
7
8
9
10
11
12

3.11、以resetlogs打开数据库

SYS@prodcdb>alter database open resetlogs;
1

3.12、开库后的操作

3.12.1、查看数据文件

SYS@prodcdb>select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/prodcdb/system01.dbf
/oradata/prodcdb/sysaux01.dbf
/oradata/prodcdb/undotbs01.dbf
/oradata/prodcdb/pdbseed/system01.dbf
/oradata/prodcdb/pdbseed/sysaux01.dbf
/oradata/prodcdb/users01.dbf
/oradata/prodcdb/pdbseed/undotbs01.dbf
/oradata/prodcdb/pdbprod1/system01.dbf
/oradata/prodcdb/pdbprod1/sysaux01.dbf
/oradata/prodcdb/pdbprod1/undotbs01.dbf
/oradata/prodcdb/pdbprod1/users01.dbf
/oradata/prodcdb/pdbprod2/system01.dbf
/oradata/prodcdb/pdbprod2/sysaux01.dbf
/oradata/prodcdb/pdbprod2/undotbs01.dbf
/oradata/prodcdb/pdbprod2/users01.dbf
/oradata/prodcdb/pdbprod1/test01.dbf

16 rows selected.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3.12.2、查看控制文件

SYS@prodcdb>select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata/prodcdb/control01.ctl
1
2
3
4
5

3.12.3、查看日志文件

SYS@prodcdb>select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata/prodcdb/redo02a.log
/oradata/prodcdb/redo01a.log

SYS@prodcdb>
1
2
3
4
5
6
7
8

3.12.4、查看redo log 信息，并删除无效日志组（rac节点2日志）

select THREAD#, STATUS, ENABLED from v$thread;
   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC

select group# from v$log where THREAD#=2;   

    GROUP#
----------
         3
         4
1
2
3
4
5
6
7
8
9
10
11
12

3.12.5、禁用thread# 2

SYS@prodcdb> alter database disable thread 2;

Database altered.
1
2
3

3.12.6、删除thread 2的3 4号日志组

SYS@prodcdb>alter database drop logfile group 3;
Database altered.
SYS@prodcdb>alter database drop logfile group  4;
Database altered.
1
2
3
4

3.12.7、查看日志组状态

SYS@prodcdb> select THREAD#, STATUS, ENABLED from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
1
2
3
4
5

3.12.8、清理undotbs2表空间

SYS@prodcdb>sho parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SYS@prodcdb> select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2

SYS@prodcdb>drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.12.9、重建temp表空间

SYS@prodcdb>select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP

SYS@prodcdb>create temporary tablespace temp1 tempfile '/oradata/prodcdb/temp01.dbf' size 100m autoextend on;

Tablespace created.
1
2
3
4
5
6
7
8

3.12.10、设置默认temp表空间

SYS@prodcdb>alter database default temporary tablespace temp1;
Database altered.
1
2

3.12.11、删除旧的temp表空间

SYS@prodcdb>drop tablespace temp including contents and datafiles;

Tablespace dropped.

SYS@prodcdb>
1
2
3
4
5

3.12.12、重建密码文件

$orapwd file=orapwprodcdb password=QWEasd123#
sqlplus / as sysdba
alter user sys identified by oracle container=all;
alter user system identified by oracle container=all;
1
2
3
4

3.12.13、配置tnsnames.ora

cd $ORACLE_HOME/network/admin
cat tnsnames.ora
pdbprod1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db19c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdbprod1)
    )
  )
pdbprod2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db19c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdbprod2)
    )
  )
prodcdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db19c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prodcdb)
    )
  )
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

3.12.14、登录pdb

pdbprod1：

sqlplus sys/oracle@pdbprod1
1
2
3

3.12.15、重建pdb的temp表空间

sqlplus sys/oracle@pdbprod1
1

3.12.16、查看temp表空间

select tablespace_name from dba_tablespaces where contents='TEMPORARY';
1

3.12.17、创建新的临时表空间

create temporary tablespace temp1 tempfile '/oradata/prodcdb/pdbprod1/temp01.dbf' size 100m autoextend on;
1

3.12.18、设置新的默认临时表空间

alter database default temporary tablespace temp1;
1

3.12.19、删除旧的临时表空间

drop tablespace temp including contents and datafiles;
1