单机转rac
先将单实例转换成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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69924215/viewspace-3029384/ ，如需转载，请注明出处，否则将追究法律责任。
