n pdb 异机恢复成pdb
先将单实例转换成rac架构
spfile没有备份，先随便写个
[oracle@test1 ~]$ cat pfile
compatible=19.0.0
db_name=SOPMG
db_block_size=8192
control_files="+data/SOPMG/controlfile/control01.ctl"
sga_target=4g
pga_aggregate_target=1g
nls_language="SIMPLIFIED CHINESE"
nls_territory="CHINA"
SOPMG1.undo_tablespace=UNDOTBS1
SOPMG2.undo_tablespace=UNDOTBS2
后面的thread undo之类的都要再调整
创建spfile，并将数据库启动到nomount状态
[oracle@test1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 1 11:17:57 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile='+data/SOPMG/spfilesop.ora' from pfile='/home/oracle/pfile';
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 4294965376 bytes
Fixed Size 8947840 bytes
Variable Size 872415232 bytes
Database Buffers 3405774848 bytes
Redo Buffers 7827456 bytes
这里需要注意创建一个init文件，检查不要留spfile文件
[oracle@test1 dbs]$ cat initSOPMG1.ora
spfile='+data/SOPMG/spfilesop.ora'
恢复控制文件，将数据库启动到mount状态
[oracle@test1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Aug 1 11:21:25 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: SOPMG (not mounted)
RMAN> restore controlfile from '/backup/ctl.bak';
Starting restore at 01-AUG-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=508 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/SOPMG/controlfile/control01.ctl
Finished restore at 01-AUG-24
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
下面进行数据文件恢复
RMAN> report schema;
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name SOPMG
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /home/oracle/db/oradata/SOPMG/system01.dbf
2 0 SSAMGT *** /home/oracle/db/oradata/SOPMG/ssamgt.dbf
3 0 SYSAUX *** /home/oracle/db/oradata/SOPMG/sysaux01.dbf
4 0 UNDOTBS1 *** /home/oracle/db/oradata/SOPMG/undotbs01.dbf
5 0 SSARUN *** /home/oracle/db/oradata/SOPMG/ssarun.dbf
7 0 USERS *** /home/oracle/db/oradata/SOPMG/users01.dbf
8 0 SSAVIEW *** /home/oracle/db/oradata/SOPMG/ssaview.dbf
9 0 PBOCD_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/PBOCD__INX.dbf
10 0 PBOCD *** /home/oracle/db/product/19.3.0/db_1/dbs/PBOCD01.dbf
11 0 DATACORE_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/datacore_INX.dbf
12 0 DATACORE *** /home/oracle/db/product/19.3.0/db_1/dbs/datacore.dbf
13 0 IRS_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_INX.dbf
14 0 IRS_IE_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_INX.dbf
15 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA.dbf
16 0 IRS_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_DATA.dbf
17 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_01.dbf
18 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_02.dbf
19 0 PBOCD *** /home/oracle/db/product/19.3.0/db_1/dbs/PBOCD02.dbf
20 0 PBOCD *** /home/oracle/db/product/19.3.0/db_1/dbs/PBOCD03.dbf
21 0 DATACORE *** /home/oracle/db/product/19.3.0/db_1/dbs/datacore_01.dbf
22 0 DATACORE *** /home/oracle/db/product/19.3.0/db_1/dbs/datacore_02.dbf
23 0 DATACORE *** /home/oracle/db/product/19.3.0/db_1/dbs/datacore_03.dbf
24 0 DATACORE *** /home/oracle/db/product/19.3.0/db_1/dbs/datacore_04.dbf
25 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_03.dbf
26 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_04.dbf
27 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_05.dbf
28 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_06.dbf
29 0 IRS_IE_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_INX_01.dbf
30 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_07.dbf
31 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_08.dbf
32 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_09.dbf
33 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_10.dbf
34 0 IRS_IE_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_INX_02.dbf
35 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_11.dbf
36 0 IRS_IE_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_INX_03.dbf
37 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_12.dbf
38 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_13.dbf
39 0 IRS_IE_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_INX_04.dbf
40 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_14.dbf
41 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_15.dbf
42 0 IRS_IE_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_INX_05.dbf
43 0 IRS_IE_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_INX_06.db
44 0 IRS_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_DATA_01.dbf
45 0 IRS_IE_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_INX_07.dbf
46 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_16.dbf
47 0 IRS_IE_INX *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_INX_08.dbf
48 0 IRS_IE_DATA *** /home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_17.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /home/oracle/db/oradata/SOPMG/temp01.dbf
2 1000 TEMP 32767 /home/oracle/db/oradata/SOPMG/temp02.dbf
3 1000 TEMP 32767 /home/oracle/db/oradata/SOPMG/temp03.dbf
RMAN> crosscheck backup;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/temp/rmanfull_orcl_SOPMG_20240725_4_1.bak RECID=1 STAMP=1175248857
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/temp/rmanfull_orcl_SOPMG_20240725_3_1.bak RECID=2 STAMP=1175248856
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/temp/archfull_orcl_202407250630pumc_1_1 RECID=4 STAMP=1175255756
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/temp/archfull_orcl_202407250730pumc_1_1 RECID=5 STAMP=1175255756
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/db/fast_recovery_area/SOPMG/autobackup/2024_07_25/o1_mf_n_1175255923_mb3mcmkq_.bkp RECID=6 STAMP=1175255923
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/temp/archfull_orcl_202407250930q6lq_1_1 RECID=7 STAMP=1175263930
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/db/fast_recovery_area/SOPMG/autobackup/2024_07_25/o1_mf_n_1175263966_mb3v6y6g_.bkp RECID=8 STAMP=1175263966
Crosschecked 7 objects
RMAN> delete expired backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 EXPIRED DISK /backup/temp/rmanfull_orcl_SOPMG_20240725_4_1.bak
2 2 1 1 EXPIRED DISK /backup/temp/rmanfull_orcl_SOPMG_20240725_3_1.bak
4 4 1 1 EXPIRED DISK /backup/temp/archfull_orcl_202407250630pumc_1_1
5 5 1 1 EXPIRED DISK /backup/temp/archfull_orcl_202407250730pumc_1_1
6 6 1 1 EXPIRED DISK /home/oracle/db/fast_recovery_area/SOPMG/autobackup/2024_07_25/o1_mf_n_1175255923_mb3mcmkq_.bkp
7 7 1 1 EXPIRED DISK /backup/temp/archfull_orcl_202407250930q6lq_1_1
8 8 1 1 EXPIRED DISK /home/oracle/db/fast_recovery_area/SOPMG/autobackup/2024_07_25/o1_mf_n_1175263966_mb3v6y6g_.bkp
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/backup/temp/rmanfull_orcl_SOPMG_20240725_4_1.bak RECID=1 STAMP=1175248857
deleted backup piece
backup piece handle=/backup/temp/rmanfull_orcl_SOPMG_20240725_3_1.bak RECID=2 STAMP=1175248856
deleted backup piece
backup piece handle=/backup/temp/archfull_orcl_202407250630pumc_1_1 RECID=4 STAMP=1175255756
deleted backup piece
backup piece handle=/backup/temp/archfull_orcl_202407250730pumc_1_1 RECID=5 STAMP=1175255756
deleted backup piece
backup piece handle=/home/oracle/db/fast_recovery_area/SOPMG/autobackup/2024_07_25/o1_mf_n_1175255923_mb3mcmkq_.bkp RECID=6 STAMP=1175255923
deleted backup piece
backup piece handle=/backup/temp/archfull_orcl_202407250930q6lq_1_1 RECID=7 STAMP=1175263930
deleted backup piece
backup piece handle=/home/oracle/db/fast_recovery_area/SOPMG/autobackup/2024_07_25/o1_mf_n_1175263966_mb3v6y6g_.bkp RECID=8 STAMP=1175263966
Deleted 7 EXPIRED objects
RMAN> catalog start with '/backup/';
searching for all files that match the pattern /backup/
List of Files Unknown to the Database
=====================================
File Name: /backup/archfull_orcl_202407250630pumc_1_1
File Name: /backup/archfull_orcl_202407250730pumc_1_1
File Name: /backup/archfull_orcl_202407250930q6lq_1_1
File Name: /backup/BLS_exp01.dmp
File Name: /backup/BLS_exp02.dmp
File Name: /backup/BLSexp.log
File Name: /backup/DZELCS_exp01.dmp
File Name: /backup/DZELCS_exp02.dmp
File Name: /backup/DZELCSexp.log
File Name: /backup/dzyhdp.dmp
File Name: /backup/dzyy_exp01.dmp
File Name: /backup/dzyy_exp02.dmp
File Name: /backup/dzyy_exp.log
File Name: /backup/feelview_exp01.dmp
File Name: /backup/feelview_exp02.dmp
File Name: /backup/feelview_exp.log
File Name: /backup/feelview_job.dmp
File Name: /backup/feelview_job.log
File Name: /backup/pfile
File Name: /backup/rmanfull_orcl_log_2024-07-25-10-00-52
File Name: /backup/rmanfull_orcl_SOPMG_20240725_3_1.bak
File Name: /backup/rmanfull_orcl_SOPMG_20240725_4_1.bak
File Name: /backup/test.tar
File Name: /backup/ZWUSER_exp01.dmp
File Name: /backup/ZWUSER_exp02.dmp
File Name: /backup/ZWUSERexp.log
File Name: /backup/ctl.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /backup/archfull_orcl_202407250630pumc_1_1
File Name: /backup/archfull_orcl_202407250730pumc_1_1
File Name: /backup/archfull_orcl_202407250930q6lq_1_1
File Name: /backup/rmanfull_orcl_SOPMG_20240725_3_1.bak
File Name: /backup/rmanfull_orcl_SOPMG_20240725_4_1.bak
File Name: /backup/ctl.bak
List of Files Which Were Not Cataloged
=======================================
File Name: /backup/BLS_exp01.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/BLS_exp02.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/BLSexp.log
RMAN-07517: Reason: The file header is corrupted
File Name: /backup/DZELCS_exp01.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/DZELCS_exp02.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/DZELCSexp.log
RMAN-07517: Reason: The file header is corrupted
File Name: /backup/dzyhdp.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/dzyy_exp01.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/dzyy_exp02.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/dzyy_exp.log
RMAN-07517: Reason: The file header is corrupted
File Name: /backup/feelview_exp01.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/feelview_exp02.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/feelview_exp.log
RMAN-07517: Reason: The file header is corrupted
File Name: /backup/feelview_job.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/feelview_job.log
RMAN-07517: Reason: The file header is corrupted
File Name: /backup/pfile
RMAN-07517: Reason: The file header is corrupted
File Name: /backup/rmanfull_orcl_log_2024-07-25-10-00-52
RMAN-07517: Reason: The file header is corrupted
File Name: /backup/test.tar
RMAN-07518: Reason: Foreign database file DBID: 0 Database Name:
File Name: /backup/ZWUSER_exp01.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/ZWUSER_exp02.dmp
RMAN-07520: Reason: Data pump dump file
File Name: /backup/ZWUSERexp.log
RMAN-07517: Reason: The file header is corrupted
调整部分参数
[oracle@test1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 1 11:30:58 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
???:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
SQL> show parameter create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter system set db_create_file_dest='+DATA';
??????
SQL> alter system set db_create_online_log_dest_1='+DATA';
??????
进行数据库restore
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
set newname for database to '+data/SOPMG/DATAFILE/%b';
restore database ;
switch datafile all;
release channel c1;
release channel c2;
}
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
restore database ;
release channel c1;
release channel c2;
}
/home/oracle/db/product/19.3.0/db_1/dbs/IRS_IE_DATA_08.dbf
恢复碰到报错
[oracle@test1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Aug 1 11:53:35 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: SOPMG (DBID=2763334154, not open)
RMAN> run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
set newname for database to '+data/SOPMG/DATAFILE/%b';
restore database ;
switch datafile all;
release channel c1;
release channel c2;
}
2> 3> 4> 5> 6> 7> 8> 9>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=1642 device type=DISK
allocated channel: c2
channel c2: SID=1768 device type=DISK
executing command: SET NEWNAME
Starting restore at 01-AUG-24
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to +data/SOPMG/DATAFILE/system01.dbf
channel c1: restoring datafile 00004 to +data/SOPMG/DATAFILE/undotbs01.dbf
channel c1: restoring datafile 00005 to +data/SOPMG/DATAFILE/ssarun.dbf
channel c1: restoring datafile 00007 to +data/SOPMG/DATAFILE/users01.dbf
channel c1: restoring datafile 00012 to +data/SOPMG/DATAFILE/datacore.dbf
channel c1: restoring datafile 00013 to +data/SOPMG/DATAFILE/IRS_INX.dbf
channel c1: restoring datafile 00015 to +data/SOPMG/DATAFILE/IRS_IE_DATA.dbf
channel c1: restoring datafile 00017 to +data/SOPMG/DATAFILE/IRS_IE_DATA_01.dbf
channel c1: restoring datafile 00019 to +data/SOPMG/DATAFILE/PBOCD02.dbf
channel c1: restoring datafile 00021 to +data/SOPMG/DATAFILE/datacore_01.dbf
channel c1: restoring datafile 00024 to +data/SOPMG/DATAFILE/datacore_04.dbf
channel c1: restoring datafile 00025 to +data/SOPMG/DATAFILE/IRS_IE_DATA_03.dbf
channel c1: restoring datafile 00027 to +data/SOPMG/DATAFILE/IRS_IE_DATA_05.dbf
channel c1: restoring datafile 00029 to +data/SOPMG/DATAFILE/IRS_IE_INX_01.dbf
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/01/2024 11:53:45
ORA-15124: ASM ??? '+data/SOPMG/DATAFILE/IRS_IE_DATA_08.dbf ' ???????
RMAN>
发现是数据文件名字有空格造成的
IRS_IE_DATA_15.dbf
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
set newname for datafile 31 to '+data/SOPMG/DATAFILE/IRS_IE_DATA_08.dbf';
set newname for datafile 33 to '+data/SOPMG/DATAFILE/IRS_IE_DATA_10.dbf';
set newname for datafile 35 to '+data/SOPMG/DATAFILE/IRS_IE_DATA_11.dbf';
set newname for datafile 39 to '+data/SOPMG/DATAFILE/IRS_IE_INX_04.dbf';
set newname for datafile 41 to '+data/SOPMG/DATAFILE/IRS_IE_DATA_15.dbf';
set newname for datafile 32 to '+data/SOPMG/DATAFILE/IRS_IE_DATA_09.dbf';
set newname for datafile 34 to '+data/SOPMG/DATAFILE/IRS_IE_INX_02.dbf';
set newname for datafile 36 to '+data/SOPMG/DATAFILE/IRS_IE_INX_03.dbf';
set newname for datafile 42 to '+data/SOPMG/DATAFILE/IRS_IE_INX_05.dbf';
set newname for datafile 38 to '+data/SOPMG/DATAFILE/IRS_IE_DATA_13.dbf';
set newname for datafile 40 to '+data/SOPMG/DATAFILE/IRS_IE_DATA_14.dbf';
set newname for database to '+data/SOPMG/DATAFILE/%b';
restore database ;
switch datafile all;
release channel c1;
release channel c2;
}
查询恢复进度：
select sid,opname,context,sofar,totalwork,round(sofar/totalwork*100,2) "%complete", time_remaining/60/60 from v$session_longops where opname like 'RMAN%' and sofar<>totalwork and totalwork!=0 ;
recover database;
RMAN> list backup of archivelog all;
备份集列表
===================
BS 关键字 大小 设备类型占用时间 完成时间
------- ---------- ----------- ------------ ----------
9 1.34G DISK 00:02:34 25-7月 -24
BP 关键字: 9 状态: AVAILABLE 已压缩: YES 标记: TAG20240725T115556
段名:/backup/archfull_orcl_202407250630pumc_1_1
备份集 9 中的已存档日志列表
线程序列 低 SCN 时间下限 下一个 SCN 下一次
---- ------- ---------- ---------- ---------- ---------
1 63310 742813065 25-7月 -24 742813304 25-7月 -24
1 63311 742813304 25-7月 -24 742813449 25-7月 -24
1 63312 742813449 25-7月 -24 742813600 25-7月 -24
1 63313 742813600 25-7月 -24 742813764 25-7月 -24
1 63314 742813764 25-7月 -24 742813915 25-7月 -24
1 63315 742813915 25-7月 -24 742814061 25-7月 -24
1 63316 742814061 25-7月 -24 742814226 25-7月 -24
1 63317 742814226 25-7月 -24 742814383 25-7月 -24
1 63318 742814383 25-7月 -24 742814548 25-7月 -24
1 63319 742814548 25-7月 -24 742814712 25-7月 -24
1 63320 742814712 25-7月 -24 742814884 25-7月 -24
1 63321 742814884 25-7月 -24 742815026 25-7月 -24
1 63322 742815026 25-7月 -24 742815227 25-7月 -24
1 63323 742815227 25-7月 -24 742815401 25-7月 -24
1 63324 742815401 25-7月 -24 742815569 25-7月 -24
1 63325 742815569 25-7月 -24 742815727 25-7月 -24
1 63326 742815727 25-7月 -24 742815890 25-7月 -24
1 63327 742815890 25-7月 -24 742816055 25-7月 -24
1 63328 742816055 25-7月 -24 742816211 25-7月 -24
1 63329 742816211 25-7月 -24 742816610 25-7月 -24
1 63330 742816610 25-7月 -24 742821965 25-7月 -24
1 63331 742821965 25-7月 -24 742827235 25-7月 -24
1 63332 742827235 25-7月 -24 742831671 25-7月 -24
1 63333 742831671 25-7月 -24 742834248 25-7月 -24
1 63334 742834248 25-7月 -24 742838626 25-7月 -24
1 63335 742838626 25-7月 -24 742842928 25-7月 -24
1 63336 742842928 25-7月 -24 742847201 25-7月 -24
1 63337 742847201 25-7月 -24 742851426 25-7月 -24
1 63338 742851426 25-7月 -24 742887822 25-7月 -24
1 63339 742887822 25-7月 -24 742891979 25-7月 -24
1 63340 742891979 25-7月 -24 742896203 25-7月 -24
1 63341 742896203 25-7月 -24 742900337 25-7月 -24
1 63342 742900337 25-7月 -24 742904776 25-7月 -24
1 63343 742904776 25-7月 -24 742907712 25-7月 -24
1 63344 742907712 25-7月 -24 742909382 25-7月 -24
BS 关键字 大小 设备类型占用时间 完成时间
------- ---------- ----------- ------------ ----------
10 1.50G DISK 00:02:41 25-7月 -24
BP 关键字: 10 状态: AVAILABLE 已压缩: YES 标记: TAG20240725T115556
段名:/backup/archfull_orcl_202407250730pumc_1_1
备份集 10 中的已存档日志列表
线程序列 低 SCN 时间下限 下一个 SCN 下一次
---- ------- ---------- ---------- ---------- ---------
1 63345 742909382 25-7月 -24 742914147 25-7月 -24
1 63346 742914147 25-7月 -24 742918923 25-7月 -24
1 63347 742918923 25-7月 -24 742923665 25-7月 -24
1 63348 742923665 25-7月 -24 742934202 25-7月 -24
1 63349 742934202 25-7月 -24 742934350 25-7月 -24
1 63350 742934350 25-7月 -24 742937589 25-7月 -24
1 63351 742937589 25-7月 -24 742940168 25-7月 -24
1 63352 742940168 25-7月 -24 742940881 25-7月 -24
1 63353 742940881 25-7月 -24 743005013 25-7月 -24
1 63354 743005013 25-7月 -24 743006244 25-7月 -24
1 63355 743006244 25-7月 -24 743006493 25-7月 -24
1 63356 743006493 25-7月 -24 743006760 25-7月 -24
1 63357 743006760 25-7月 -24 743007381 25-7月 -24
1 63358 743007381 25-7月 -24 743007953 25-7月 -24
1 63359 743007953 25-7月 -24 743010373 25-7月 -24
1 63360 743010373 25-7月 -24 743010744 25-7月 -24
1 63361 743010744 25-7月 -24 743013154 25-7月 -24
1 63362 743013154 25-7月 -24 743014617 25-7月 -24
1 63363 743014617 25-7月 -24 743015920 25-7月 -24
1 63364 743015920 25-7月 -24 743016254 25-7月 -24
1 63365 743016254 25-7月 -24 743025600 25-7月 -24
1 63366 743025600 25-7月 -24 743025886 25-7月 -24
1 63367 743025886 25-7月 -24 743026139 25-7月 -24
1 63368 743026139 25-7月 -24 743026759 25-7月 -24
1 63369 743026759 25-7月 -24 743027230 25-7月 -24
1 63370 743027230 25-7月 -24 743028631 25-7月 -24
1 63371 743028631 25-7月 -24 743029187 25-7月 -24
1 63372 743029187 25-7月 -24 743030366 25-7月 -24
1 63373 743030366 25-7月 -24 743035389 25-7月 -24
1 63374 743035389 25-7月 -24 743037096 25-7月 -24
1 63375 743037096 25-7月 -24 743038235 25-7月 -24
1 63376 743038235 25-7月 -24 743038668 25-7月 -24
1 63377 743038668 25-7月 -24 743038679 25-7月 -24
1 63378 743038679 25-7月 -24 743038690 25-7月 -24
1 63379 743038690 25-7月 -24 743038698 25-7月 -24
BS 关键字 大小 设备类型占用时间 完成时间
------- ---------- ----------- ------------ ----------
11 317.96M DISK 00:00:33 25-7月 -24
BP 关键字: 11 状态: AVAILABLE 已压缩: YES 标记: TAG20240725T141210
段名:/backup/archfull_orcl_202407250930q6lq_1_1
备份集 11 中的已存档日志列表
线程序列 低 SCN 时间下限 下一个 SCN 下一次
---- ------- ---------- ---------- ---------- ---------
1 63380 743038698 25-7月 -24 743059857 25-7月 -24
1 63381 743059857 25-7月 -24 743072260 25-7月 -24
1 63382 743072260 25-7月 -24 743080595 25-7月 -24
1 63383 743080595 25-7月 -24 743080732 25-7月 -24
1 63384 743080732 25-7月 -24 743081571 25-7月 -24
1 63385 743081571 25-7月 -24 743081957 25-7月 -24
1 63386 743081957 25-7月 -24 743082110 25-7月 -24
1 63387 743082110 25-7月 -24 743108742 25-7月 -24
1 63388 743108742 25-7月 -24 743109043 25-7月 -24
1 63389 743109043 25-7月 -24 743109056 25-7月 -24
1 63390 743109056 25-7月 -24 743109069 25-7月 -24
1 63391 743109069 25-7月 -24 743109082 25-7月 -24
1 63392 743109082 25-7月 -24 743109095 25-7月 -24
1 63393 743109095 25-7月 -24 743109108 25-7月 -24
1 63394 743109108 25-7月 -24 743109120 25-7月 -24
RMAN>
RMAN>
RMAN> recover database until scn 743109120;
从位于 01-8月 -24 的 recover 开始
使用通道 ORA_DISK_1
正在开始介质的恢复
通道 ORA_DISK_1: 正在开始将归档日志还原到默认目标
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63381
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63382
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63383
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63384
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63385
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63386
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63387
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63388
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63389
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63390
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63391
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63392
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63393
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 63394
通道 ORA_DISK_1: 正在读取备份片段 /backup/archfull_orcl_202407250930q6lq_1_1
通道 ORA_DISK_1: 片段句柄 = /backup/archfull_orcl_202407250930q6lq_1_1 标记 = TAG20240725T141210
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:45
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63381.329.1175875639 线程 = 1 序列 = 63381
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63382.330.1175875639 线程 = 1 序列 = 63382
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63383.331.1175875639 线程 = 1 序列 = 63383
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63384.334.1175875639 线程 = 1 序列 = 63384
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63385.332.1175875639 线程 = 1 序列 = 63385
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63386.328.1175875639 线程 = 1 序列 = 63386
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63387.333.1175875639 线程 = 1 序列 = 63387
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63388.335.1175875673 线程 = 1 序列 = 63388
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63389.336.1175875673 线程 = 1 序列 = 63389
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63390.337.1175875673 线程 = 1 序列 = 63390
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63391.338.1175875673 线程 = 1 序列 = 63391
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63392.339.1175875673 线程 = 1 序列 = 63392
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63393.340.1175875673 线程 = 1 序列 = 63393
归档日志文件名 = +DATA/SOPMG/ARCHIVELOG/2024_08_01/thread_1_seq_63394.341.1175875673 线程 = 1 序列 = 63394
介质恢复完成, 用时: 00:01:05
在 01-8月 -24 完成了 recover
RMAN>
RMAN>
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: 位于 08/01/2024 16:11:45 的 sql statement 命令失败
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项
RMAN> alter database open resetlogs;
已处理语句
TARGET 数据库中 PL/SQL 程序包 SYS.DBMS_BACKUP_RESTORE 的版本 19.03.00.00 并非最新
TARGET 数据库中 PL/SQL 程序包 SYS.DBMS_RCVMAN 的版本 19.03.00.00 并非最新
###############################################################################################################
至此 数据库以及启动，后面需要将单实例转换成rac
修改参数文件：
[oracle@test1 ~]$ cat pfile
*.compatible='19.0.0'
*.control_files='+DATA/SOPMG/controlfile/control01.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_create_online_log_dest_1='+DATA'
*.db_name='SOPMG'
*.log_archive_dest_1='LOCATION=+DATA'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.pga_aggregate_target=1g
*.sga_target=4g
SOPMG1.undo_tablespace='UNDOTBS1'
SOPMG2.undo_tablespace='UNDOTBS2'
SOPMG1.instance_number=1
SOPMG2.instance_number=2
SOPMG1.thread=1
SOPMG2.thread=2
*.remote_listener='testscan:1521'
cluster_database=true
cluster_database_instances=2
重要的就是undo redo thread 和remote scan cluster_database
添加thread redo 和undo
创建undo
SQL> create undo tablespace UNDOTBS2 datafile size 100m autoextend on;
表空间已创建。
创建redo
SQL> select group#,thread#, bytes/1024/1024 from v$log;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 200
2 1 200
3 1 200
SQL> alter database add logfile thread 2 group 4 size 200m;
alter database add logfile thread 2 group 5 size 200m;
alter database add logfile thread 2 group 6 size 200m;
数据库已更改。
SQL>
数据库已更改。
SQL>
数据库已更改。
SQL> SQL>
SQL>
SQL> select group#,thread#, bytes/1024/1024 from v$log;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 200
2 1 200
3 1 200
4 2 200
5 2 200
6 2 200
已选择 6 行。
查看thread
select thread#,status,enabled from v$thread;
SQL> select thread#,status,enabled from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED DISABLED
启动thread
SQL> alter database enable public thread 2;
数据库已更改。
SQL> select thread#,status,enabled from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PUBLIC
1节点重启，重新创建spfile
[oracle@test1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on 星期四 8月 1 16:36:01 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> quit
从 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0 断开
[oracle@test1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on 星期四 8月 1 16:37:22 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
已连接到空闲例程。
SQL> create spfile='+DATA/SOPMG/spfilesop.ora' from pfile='/home/oracle/pfile';
File created.
-->一定要建立到asm里面
SQL>
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 4294965376 bytes
Fixed Size 8947840 bytes
Variable Size 922746880 bytes
Database Buffers 3355443200 bytes
Redo Buffers 7827456 bytes
数据库装载完毕。
数据库已经打开。
SQL> SQL>
2节点创建init文件
[oracle@test2 dbs]$ cat initSOPMG2.ora
spfile='+data/SOPMG/spfilesop.ora'
启动2节点：
报错：
SQL> startup
ORA-00304: requested INSTANCE_NUMBER is busy
重新设置下面的参数
alter system set instance_number=1 sid='SOPMG1' scope=spfile;
alter system set instance_number=2 sid='SOPMG2' scope=spfile;
alter system set thread=1 sid='SOPMG1' scope=spfile;
alter system set thread=2 sid='SOPMG2' scope=spfile;
重启2节点报错
SQL> startup mount force
ORA-29707: inconsistent value 18446744073709551615 for initialization parameter 1233 with other instances
检查发现1节点用了本地的spfile 和2节点的spfile 不是一个，修改后重新启动
1 节点
[oracle@test1 ~]$ cd $ORACLE_HOME
[oracle@test1 db_1]$ cd dbs/
[oracle@test1 dbs]$ ll
总用量 124836
-rw-r----- 1 oracle asmadmin 39845888 8月 1 16:12 c-2763334154-20240801-01
-rw-r----- 1 oracle asmadmin 39845888 8月 1 16:48 c-2763334154-20240801-02
-rw-rw---- 1 oracle asmadmin 1544 7月 25 18:40 hc_dzyy1.dat
-rw-rw---- 1 oracle asmadmin 1544 8月 1 16:59 hc_SOPMG1.dat
-rw-r----- 1 oracle asmadmin 8388608 8月 1 17:00 id_dzyy1.dat
-rw-r--r-- 1 oracle oinstall 3079 5月 14 2015 init.ora
-rw-r--r-- 1 oracle oinstall 35 8月 1 10:48 initSOPMG1.ora
-rw-r----- 1 oracle asmadmin 0 8月 1 16:59 lkinstSOPMG1
-rw-r----- 1 oracle asmadmin 39731200 8月 1 16:48 snapcf_SOPMG1.f
[oracle@test1 dbs]$ cat initSOPMG1.ora
spfile='+data/SOPMG/spfilesop.ora'
[oracle@test1 dbs]$
2 节点
[oracle@test2 dbs]$ pwd
/u02/app/oracle/product/19.9.0/db_1/dbs
[oracle@test2 dbs]$ ll
total 8208
-rw-rw---- 1 oracle asmadmin 1544 Jul 25 18:40 hc_dzyy2.dat
-rw-rw---- 1 oracle asmadmin 1544 Aug 1 16:56 hc_SOPMG2.dat
-rw-r----- 1 oracle asmadmin 8388608 Aug 1 17:01 id_dzyy2.dat
-rw-r--r-- 1 oracle oinstall 3079 Jul 23 18:15 init.ora
-rw-r--r-- 1 oracle oinstall 35 Aug 1 16:39 initSOPMG2.ora
[oracle@test2 dbs]$ cat initSOPMG2.ora
spfile='+data/SOPMG/spfilesop.ora'
刚才启动还发现了几个问题
thread 1 的redo位置不对
redo重建
select 'alter database rename file ''' ||member||''''||' to '''||'+DATA'''||';' from v$logfile;
alter database add logfile thread 1 group 7 size 200m;
alter database add logfile thread 1 group 8 size 200m;
alter database add logfile thread 1 group 9 size 200m;
SQL> select member from v$logfile;
/home/oracle/db/oradata/SOPMG/redo03.log
/home/oracle/db/oradata/SOPMG/redo02.log
+DATA/SOPMG/ONLINELOG/group_7.358.1175880465
+DATA/SOPMG/ONLINELOG/group_4.351.1175877139
+DATA/SOPMG/ONLINELOG/group_5.352.1175877139
+DATA/SOPMG/ONLINELOG/group_6.353.1175877139
+DATA/SOPMG/ONLINELOG/group_8.359.1175880465
+DATA/SOPMG/ONLINELOG/group_9.360.1175880465
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
--》删除3的时候删除不了，启动数据库 切换日志再次删除
alter system archive log current;
不行就checkpoint
alter system checkpoint;
select status , GROUP#,THREAD# from v$log;
alter database drop logfile group 3;
tempfile rename
select 'alter database rename file ''' ||name||''''||' to '''||'+DATA'''||';' from v$tempfile;
alter database rename file '/home/oracle/db/oradata/SOPMG/temp01.dbf' to '+DATA';
alter database rename file '/home/oracle/db/oradata/SOPMG/temp02.dbf' to '+DATA';
alter database rename file '/home/oracle/db/oradata/SOPMG/temp03.dbf' to '+DATA';
跑下视图的相关脚本：
@?/rdbms/admin/catclust.sql
启动两个节点，看看能否正常启动，
下面，将数据库注册进集群
srvctl add database -d SOPMG -o /u02/app/oracle/product/19.9.0/db_1 -c RAC -p +DATA/SOPMG/spfilesop.ora
srvctl add instance -d SOPMG -i SOPMG1 -n test1
srvctl add instance -d SOPMG -i SOPMG2 -n test2
步骤如下：
在此测试案例中：
Non-CDB 数据库的名称是：SOPMG
1. 要将 non-CDB 转换为 PDB，必须彻底关闭数据库：
将环境变量设置为 SOPMG sid SOPMG1 或者2
sqlplus / as sysdba
sql> shutdown immediate
2. 彻底关闭数据库后，以只读模式打开它：
sql> startup open read only
3. 描述数据库并生成 xml 文件：
BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/SOPMGNonPDB.xml');
END;
/
4. 关闭数据库：
sql> shutdown immediate
5. 在目标 CDB 环境中执行下面的操作来检查兼容性：
SET SERVEROUTPUT ON;
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/SOPMGNonPDB.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
如果有报错，可以通过 cdb 数据库的 PDB_PLUG_IN_VIOLATIONS 查看。
col cause for a20
col name for a20
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='SOPMG';
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- ----------------------------------- ---------
SOPMG Non-CDB to PDB WARNING PDB plugged in is a non-CDB, PENDING
requires noncdb_to_pdb.sql be run.
SOPMG OPTION WARNING Database option RAC mismatch: PDB PENDING
installed version NULL. CDB
installed version 19.0.0.0.0.
SOPMG Parameter WARNING CDB parameter nls_language PENDING
mismatch: Previous 'SIMPLIFIED
CHINESE' Current 'AMERICAN'
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- ----------------------------------- ---------
SOPMG Parameter WARNING CDB parameter nls_territory PENDING
mismatch: Previous 'CHINA' Current
'AMERICA'
SOPMG Parameter WARNING CDB parameter sga_target mismatch: PENDING
Previous 4G Current 9568M
SOPMG SQL Patch ERROR Interim patch 36199232/25601966 PENDING
(OJVM RELEASE UPDATE:
19.23.0.0.240416 (36199232)):
Installed in the CDB but not in the
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- ----------------------------------- ---------
PDB
SOPMG SQL Patch ERROR '19.23.0.0.0 Release_Update PENDING
2404060042' is installed in the CDB
but '19.3.0.0.0 Release_Update
1904101227' is installed in the PDB
7 rows selected.
处理下上述问题：
问题1： 后面再跑
问题2： 查看下组件情况，我们是单机转的rac，这里需要再跑下脚本
col comp_name format a50
select comp_name,version,status from dba_registry;
Oracle Database Catalog Views 19.0.0.0.0 VALID
Oracle Database Packages and Types 19.0.0.0.0 VALID
Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
JServer JAVA Virtual Machine 19.0.0.0.0 VALID
Oracle XDK 19.0.0.0.0 VALID
Oracle Database Java Packages 19.0.0.0.0 VALID
OLAP Analytic Workspace 19.0.0.0.0 VALID
Oracle XML Database 19.0.0.0.0 VALID
Oracle Workspace Manager 19.0.0.0.0 VALID
Oracle Text 19.0.0.0.0 VALID
Oracle Multimedia 19.0.0.0.0 VALID
Spatial 19.0.0.0.0 VALID
Oracle OLAP API 19.0.0.0.0 VALID
Oracle Label Security 19.0.0.0.0 VALID
Oracle Database Vault 19.0.0.0.0 VALID
这里是option off
@?/rdbms/admin/catclust.sql
再次检查：
col comp_name format a50
select comp_name,version,status from dba_registry;SQL> SQL>
Oracle Database Catalog Views 19.0.0.0.0 VALID
Oracle Database Packages and Types 19.0.0.0.0 VALID
Oracle Real Application Clusters 19.0.0.0.0 VALID
JServer JAVA Virtual Machine 19.0.0.0.0 VALID
Oracle XDK 19.0.0.0.0 VALID
Oracle Database Java Packages 19.0.0.0.0 VALID
OLAP Analytic Workspace 19.0.0.0.0 VALID
Oracle XML Database 19.0.0.0.0 VALID
Oracle Workspace Manager 19.0.0.0.0 VALID
Oracle Text 19.0.0.0.0 VALID
Oracle Multimedia 19.0.0.0.0 VALID
Spatial 19.0.0.0.0 VALID
Oracle OLAP API 19.0.0.0.0 VALID
Oracle Label Security 19.0.0.0.0 VALID
Oracle Database Vault 19.0.0.0.0 VALID
SQL> select count(*) ,owner from dba_objects where status !='VALID' group by owner;
37 PBOCD
18 IRS
1 SOP
19 DATACORE
4 ZHYH
内存可以忽略
nls 设置暂时忽略
再次执行上面的一到5
如果出现以下错误：
ERROR PSU bundle patch 1 (PSU Patch 12345): Installed in the CDB but not in the PDB.
ERROR PSU bundle patch 1 (PSU Patch 12345): Installed in the PDB but not in the CDB.
您需要参考 Note 1935365.1 来修复。
6. 连接到目标 CDB：
. oraen
将环境变量设置为 dzyy
sqlplus / as sysdba
7. 创建 pluggable database：
CREATE PLUGGABLE DATABASE SOPMGPDB USING '/tmp/SOPMGNonPDB.xml'
COPY
FILE_NAME_CONVERT = ('+DATA/SOPMG/DATAFILE/', '+DATA/DZYY/SOPMG/'，'+DATA/SOPMG/TEMPFILE/', '+DATA/DZYY/SOPMG/');
-->空间不够 copy 改成了move
CREATE PLUGGABLE DATABASE SOPMGPDB USING '/tmp/SOPMGNonPDB.xml'
move
FILE_NAME_CONVERT = (
'+DATA/SOPMG/DATAFILE/datacore.dbf' , '+DATA/DZYY/SOPMG/datacore.dbf',
'+DATA/SOPMG/DATAFILE/datacore_01.dbf' , '+DATA/DZYY/SOPMG/datacore_01.dbf',
'+DATA/SOPMG/DATAFILE/datacore_02.dbf' , '+DATA/DZYY/SOPMG/datacore_02.dbf',
'+DATA/SOPMG/DATAFILE/datacore_03.dbf' , '+DATA/DZYY/SOPMG/datacore_03.dbf',
'+DATA/SOPMG/DATAFILE/datacore_04.dbf' , '+DATA/DZYY/SOPMG/datacore_04.dbf',
'+DATA/SOPMG/DATAFILE/datacore_inx.dbf' , '+DATA/DZYY/SOPMG/datacore_inx.dbf',
'+DATA/SOPMG/DATAFILE/irs_data.dbf' , '+DATA/DZYY/SOPMG/irs_data.dbf',
'+DATA/SOPMG/DATAFILE/irs_data_01.dbf' , '+DATA/DZYY/SOPMG/irs_data_01.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_01.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_01.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_02.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_02.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_03.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_03.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_04.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_04.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_05.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_05.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_06.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_06.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_07.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_07.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_08.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_08.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_09.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_09.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_10.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_10.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_11.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_11.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_12.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_12.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_13.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_13.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_14.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_14.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_15.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_15.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_16.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_16.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_data_17.dbf' , '+DATA/DZYY/SOPMG/irs_ie_data_17.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_inx.dbf' , '+DATA/DZYY/SOPMG/irs_ie_inx.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_inx_01.dbf' , '+DATA/DZYY/SOPMG/irs_ie_inx_01.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_inx_02.dbf' , '+DATA/DZYY/SOPMG/irs_ie_inx_02.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_inx_03.dbf' , '+DATA/DZYY/SOPMG/irs_ie_inx_03.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_inx_04.dbf' , '+DATA/DZYY/SOPMG/irs_ie_inx_04.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_inx_05.dbf' , '+DATA/DZYY/SOPMG/irs_ie_inx_05.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_inx_06.db' , '+DATA/DZYY/SOPMG/irs_ie_inx_06.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_inx_07.dbf' , '+DATA/DZYY/SOPMG/irs_ie_inx_07.dbf',
'+DATA/SOPMG/DATAFILE/irs_ie_inx_08.dbf' , '+DATA/DZYY/SOPMG/irs_ie_inx_08.dbf',
'+DATA/SOPMG/DATAFILE/irs_inx.dbf' , '+DATA/DZYY/SOPMG/irs_inx.dbf',
'+DATA/SOPMG/DATAFILE/pbocd01.dbf' , '+DATA/DZYY/SOPMG/pbocd01.dbf',
'+DATA/SOPMG/DATAFILE/pbocd02.dbf' , '+DATA/DZYY/SOPMG/pbocd02.dbf',
'+DATA/SOPMG/DATAFILE/pbocd03.dbf' , '+DATA/DZYY/SOPMG/pbocd03.dbf',
'+DATA/SOPMG/DATAFILE/pbocd__inx.dbf' , '+DATA/DZYY/SOPMG/pbocd__inx.dbf',
'+DATA/SOPMG/DATAFILE/ssamgt.dbf' , '+DATA/DZYY/SOPMG/ssamgt.dbf',
'+DATA/SOPMG/DATAFILE/ssarun.dbf' , '+DATA/DZYY/SOPMG/ssarun.dbf',
'+DATA/SOPMG/DATAFILE/ssaview.dbf' , '+DATA/DZYY/SOPMG/ssaview.dbf',
'+DATA/SOPMG/DATAFILE/sysaux01.dbf' , '+DATA/DZYY/SOPMG/sysaux01.dbf',
'+DATA/SOPMG/DATAFILE/system01.dbf' , '+DATA/DZYY/SOPMG/system01.dbf',
'+DATA/SOPMG/DATAFILE/undotbs01.dbf' , '+DATA/DZYY/SOPMG/undotbs01.dbf',
'+DATA/SOPMG/DATAFILE/undotbs2.350.1175876965' , '+DATA/DZYY/SOPMG/undotbs2.dbf',
'+DATA/SOPMG/DATAFILE/users01.dbf' , '+DATA/DZYY/SOPMG/users01.dbf',
'+DATA/SOPMG/TEMPFILE/temp.361.1175880529' , '+DATA/DZYY/SOPMG/temp01.dbf',
'+DATA/SOPMG/TEMPFILE/temp.362.1175880529' , '+DATA/DZYY/SOPMG/temp02.dbf',
'+DATA/SOPMG/TEMPFILE/temp.363.1175880529' , '+DATA/DZYY/SOPMG/temp03.dbf');
select ' ''' ||name||''''||' , '''||'+DATA/DZYY/SOPMG'''||',' from v$tempfile union select ' ''' ||name||''''||' , '''||'+DATA/DZYY/SOPMG'''||',' from v$datafile;
asm里面创建 +DATA/DZYY/SOPMG 这个目录
8. 切换到 PDB container 并运行 "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql"
sql> ALTER SESSION SET CONTAINER=SOPMGPDB ;
sql> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
ALTER SESSION SET CONTAINER=SOPMGPDB ;
select event,seconds_in_wait from v$session where username='SYS';
9. 启动 PDB 并检查 open mode：
ALTER PLUGGABLE DATABASE SOPMGPDB OPEN;
SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
APDB READ WRITE
1 row selected.
SQL>
SQL> select message,time,status from pdb_plug_in_violations;
MESSAGE
-----------------------------------
TIME
---------------------------------------------------------------------------
PDB plugged in is a non-CDB,
requires noncdb_to_pdb.sql be run.
01-AUG-24 07.13.37.156128 PM
Interim patch 36199232/25601966
(OJVM RELEASE UPDATE:
19.23.0.0.240416 (36199232)):
Installed in the CDB but not in the
PDB
01-AUG-24 07.13.39.376547 PM
'19.23.0.0.0 Release_Update
2404060042' is installed in the CDB
but '19.3.0.0.0 Release_Update
1904101227' is installed in the PDB
01-AUG-24 07.13.39.377048 PM
新进去的pdb没有打补丁，需要重新打补丁
需要先编译无效对象然后再跑
@?/rdbms/admin/utlrp.sql
[oracle@test1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.23.0.0.0 Production on Thu Aug 1 19:26:30 2024
Copyright (c) 2012, 2024, Oracle. All rights reserved.
Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_26536_2024_08_01_19_26_30/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 36199232 (OJVM RELEASE UPDATE: 19.23.0.0.240416 (36199232)):
Binary registry: Installed
PDB CDB$ROOT: Applied successfully on 25-JUL-24 06.13.57.623419 PM
PDB PDB: Applied successfully on 25-JUL-24 06.30.53.864913 PM
PDB PDB$SEED: Applied successfully on 25-JUL-24 06.30.53.864913 PM
PDB SOPMGPDB: Not installed
Current state of release update SQL patches:
Binary registry:
19.23.0.0.0 Release_Update 240406004238: Installed
PDB CDB$ROOT:
Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 25-JUL-24 06.23.20.446576 PM
PDB PDB:
Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 25-JUL-24 06.37.24.104069 PM
PDB PDB$SEED:
Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 25-JUL-24 06.37.24.104069 PM
PDB SOPMGPDB:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 18-JUL-19 02.53.02.271588 AM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB
No interim patches need to be rolled back
No release update patches need to be installed
No interim patches need to be applied
For the following PDBs: SOPMGPDB
No interim patches need to be rolled back
Patch 36233263 (Database Release Update : 19.23.0.0.240416 (36233263)):
Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.23.0.0.0 Release_Update 240406004238
The following interim patches will be applied:
36199232 (OJVM RELEASE UPDATE: 19.23.0.0.240416 (36199232))
WARNING: Following components are NOT in a valid state.
This could cause patching failure. If it does, consider
running utlrp.sql to bring components to VALID state.
Then, re-run datapatch.
SOPMGPDB : CATPROC [INVALID], OLS [INVALID], SDO [INVALID]
Installing patches...
@?/rdbms/admin/utlrp.sql
源数据字符集（NLS_CHARACTERSET）需要与目标环境 CDB 的 NLS_CHARACTERSET 相同，或者源数据库字符集是目标环境 CDB 的 NLS_CHARACTERSET 的二进制子集，源和目标的 NLS_NCHAR_CHARACTERSET 需要相同。
如果 PDB 是 Unicode 字符集，建议创建字符集为 AL32UTF8 的 CDB。请注意，我们无法使用 DMU 迁移 CDB 的字符集。
参考：
12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1)
Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET ) (Doc ID 225912.1) [Section E]
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69924215/viewspace-3029383/ ，如需转载，请注明出处，否则将追究法律责任。
