oracle 19c rac 异机恢复到单机19c ，包含pdb

目标端操作：
安装操作系统，安装数据库软件，不建库。
创建相关目录：

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
复制

源端操作：
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';
复制

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

准备pfile文件：

sqlplus / as sysdba
create pfile=/home/oracle/pfile.ora from spfile;
复制

修改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 ~]$
复制

目的端操作：
准备pifle文件：同上

sqlplus  /  as sysdba
create spfile from pfile;
startup nomount
复制

另开一个窗口：
rman恢复控制文件：

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

启动到mount：

alter database mount;
复制

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

catalog start  with '/home/oracle/archivelog';
复制

查看备份的数据文件：

list backup;
复制

查看备份的归档日志文件:

list backup of archivelog all;
复制

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

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>
复制

执行恢复操作：
指定最新的archivelog sequence 号是上面查看到的29号;
将数据文件重命名,根据list backup的文件名进行相应的修改；
还原数据文件；
切换到新的数据文件路径；
恢复数据库；

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;
}
复制

恢复完数据库是mount状态；
查看日志文件并重命名到新的路径：

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>
复制

重命名到新路径

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
复制

以resetlogs开库：

SYS@prodcdb>alter database open resetlogs;
复制

开库后的操作：
查看数据文件：

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.
复制

查看控制文件：

SYS@prodcdb>select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata/prodcdb/control01.ctl
复制

查看日志文件：

SYS@prodcdb>select member from v$logfile;

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

SYS@prodcdb>
复制

查看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
复制

禁用thread# 2

SYS@prodcdb> alter database disable thread 2;

Database altered.
复制

删除thread 2的3 4号日志组：

SYS@prodcdb>alter database drop logfile group 3;
Database altered.
SYS@prodcdb>alter database drop logfile group  4;
Database altered.
复制

查看日志组状态：

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

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
复制

清理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.
复制

重建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.
复制

设置默认temp表空间：

SYS@prodcdb>alter database default temporary tablespace temp1;
Database altered.
复制

删除旧的temp表空间：

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

Tablespace dropped.

SYS@prodcdb>
复制

重建密码文件：

$orapwd file=orapwprodcdb password=QWEasd123#
sqlplus / as sysdba
alter user sys identified by oracle container=all;
alter user system identified by oracle container=all;
复制

配置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)
    )
  )
复制

登录pdb:
pdbprod1：

sqlplus sys/oracle@pdbprod1
复制

重建pdb的temp表空间：

sqlplus sys/oracle@pdbprod1
复制

查看temp表空间：

select tablespace_name from dba_tablespaces where contents='TEMPORARY';
复制

创建新的临时表空间：

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

设置新的默认临时表空间：

alter database default temporary tablespace temp1;
复制

删除旧的临时表空间：

drop tablespace temp including contents and datafiles;
复制

pdbprod2 操作同上！