测试环境： Oracle 19.12 RAC + Redhat 7.9
源库：
db_name=cxmtdb SID=cxmtdb1/cxmtdb2
IP: 192.168.56.200/201
目标库,目标库只安装软件，不创建库。
db_name=cxmtdb SID=cxmtdb
IP：192.168.56.101
1.从生产库创建PFILE
create pfile='/tmp/pfile.ora' from spfile;2.备份源端数据库
[oracle@rac01 ~]$ rman target /
RMAN> backup database format '/backup/datafile_%U';
RMAN> backup archivelog all format '/backup/arch_%U';
RMAN> backup current controlfile format '/backup/control.bak';3.编辑参数文件，修改成单机的，传至目标环境
```shell
[root@db01 ~]# mkdir /oradata
[root@db01 ~]# mkdir /archivelog
[root@db01 ~]# chown oracle.oinstall /oradata/
[root@db01 ~]# chown oracle.oinstall /archivelog/
[oracle@db01 tmp]$ cat /tmp/pfile.ora
cxmtdb.__data_transfer_cache_size=0
cxmtdb.__db_cache_size=1174405120
cxmtdb.__inmemory_ext_roarea=0
cxmtdb.__inmemory_ext_rwarea=0
cxmtdb.__java_pool_size=201326592
cxmtdb.__large_pool_size=100663296
cxmtdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
cxmtdb.__pga_aggregate_target=838860800
cxmtdb.__sga_target=2516582400
cxmtdb.__shared_io_pool_size=117440512
cxmtdb.__shared_pool_size=872415232
cxmtdb.__streams_pool_size=33554432
cxmtdb.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/cxmtdb/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oradata/cxmtdb/control01.ctl','/oradata/cxmtdb/control02.ctl'#Restore Controlfile
*.db_block_size=6144
*.db_name='cxmtdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cxmtdbXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_CXMTDB'
*.log_archive_dest_1='location=/archivelog'
*.nls_date_format='yyyy-mm-dd hh24:mi:ss'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=798m
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1796m
*.undo_tablespace='UNDOTBS1'4.目标端操作
sqlplus / as sysdba
create spfile from pfile='/tmp/pfile.ora';
startup nomount5.恢复controlfile
[oracle@db01 tmp]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Oct 3 20:14:53 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CXMTDB (not mounted)
RMAN> restore controlfile from '/backup/control.bak';6.启动到mount：
https://www.cndba.cn/hbhe0316/article/4859
SQL> alter database mount;
Database altered.7.注册新增加的归档日志文件：
RMAN> catalog start with '/backup';8.查看注册的新的归档日志文件：
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name CXMTDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ------------------
14 1 20 A 2021:10:0318:58:17
Name: +DATA/cxmtdb/arch/1_20_1083760352.dbf
15 1 21 A 2021:10:0318:58:35
Name: +DATA/cxmtdb/arch/1_21_1083760352.dbf
18 1 22 A 2021:10:0318:58:37
Name: +DATA/cxmtdb/arch/1_22_1083760352.dbf
21 1 23 A 2021:10:0322:04:59
Name: +DATA/cxmtdb/arch/1_23_1083760352.dbf
22 1 24 A 2021:10:0410:11:44
Name: +DATA/cxmtdb/arch/1_24_1083760352.dbf
27 1 25 A 2021:10:0410:15:26
Name: /archivelog/1_25_1083760352.dbf
24 1 25 A 2021:10:0410:15:26
Name: +DATA/cxmtdb/arch/1_25_1083760352.dbf
13 2 9 A 2021:10:0318:58:19
Name: +DATA/cxmtdb/arch/2_9_1083760352.dbf
16 2 10 A 2021:10:0318:58:35
Name: +DATA/cxmtdb/arch/2_10_1083760352.dbf
17 2 11 A 2021:10:0318:58:44
Name: +DATA/cxmtdb/arch/2_11_1083760352.dbf
19 2 12 A 2021:10:0322:05:01
Name: +DATA/cxmtdb/arch/2_12_1083760352.dbf
20 2 13 A 2021:10:0410:07:00
Name: +DATA/cxmtdb/arch/2_13_1083760352.dbf
23 2 14 A 2021:10:0410:11:43
Name: +DATA/cxmtdb/arch/2_14_1083760352.dbf
26 2 15 A 2021:10:0410:15:29
Name: /archivelog/2_15_1083760352.dbf
25 2 15 A 2021:10:0410:15:29
Name: +DATA/cxmtdb/arch/2_15_1083760352.dbf9.源端操作https://www.cndba.cn/hbhe0316/article/4859
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
306012510.目标端操作https://www.cndba.cn/hbhe0316/article/4859
[oracle@db01 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Oct 4 11:20:47 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CXMTDB (DBID=2538077276, not open)
RMAN> report schema;
using target database control file instead of recovery catalog
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name CXMTDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** +DATA/CXMTDB/DATAFILE/system.257.1083760203
3 0 SYSAUX *** +DATA/CXMTDB/DATAFILE/sysaux.258.1083760247
4 0 UNDOTBS1 *** +DATA/CXMTDB/DATAFILE/undotbs1.259.1083760273
5 0 PDB$SEED:SYSTEM *** +DATA/CXMTDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.265.1083760879
6 0 PDB$SEED:SYSAUX *** +DATA/CXMTDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.266.1083760879
7 0 USERS *** +DATA/CXMTDB/DATAFILE/users.260.1083760275
8 0 PDB$SEED:UNDOTBS1 *** +DATA/CXMTDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.267.1083760879
9 0 UNDOTBS2 *** +DATA/CXMTDB/DATAFILE/undotbs2.269.1083761287
10 0 HBHE:SYSTEM *** +DATA/CXMTDB/CC672D9C261E0D74E053C838A8C0C649/DATAFILE/system.274.1083762133
11 0 HBHE:SYSAUX *** +DATA/CXMTDB/CC672D9C261E0D74E053C838A8C0C649/DATAFILE/sysaux.275.1083762133
12 0 HBHE:UNDOTBS1 *** +DATA/CXMTDB/CC672D9C261E0D74E053C838A8C0C649/DATAFILE/undotbs1.273.1083762131
13 0 HBHE:UNDO_2 *** +DATA/CXMTDB/CC672D9C261E0D74E053C838A8C0C649/DATAFILE/undo_2.277.1083762283
14 0 HBHE:USERS *** +DATA/CXMTDB/CC672D9C261E0D74E053C838A8C0C649/DATAFILE/users.278.1083762289
15 0 TBS01 *** +DATA/CXMTDB/DATAFILE/tbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/CXMTDB/TEMPFILE/temp.264.1083760389
2 36 PDB$SEED:TEMP 32767 +DATA/CXMTDB/CC66E413E9F85128E053C838A8C0F16E/TEMPFILE/temp.268.1083760911
3 36 HBHE:TEMP 32767 +DATA/CXMTDB/CC672D9C261E0D74E053C838A8C0C649/TEMPFILE/temp.276.1083762191
run{
set until sequence 26 thread 1;
set NEWNAME for datafile 1 to '/oradata/cxmtdb/system01.dbf';
set NEWNAME for datafile 3 to '/oradata/cxmtdb/sysaux01.dbf';
set newname for datafile 4 to '/oradata/cxmtdb/undotbs01.dbf';
set newname for datafile 5 to '/oradata/cxmtdb/pdbseed/system01.dbf';
set newname for datafile 6 to '/oradata/cxmtdb/pdbseed/sysaux01.dbf';
set newname for datafile 7 to '/oradata/cxmtdb/users01.dbf';
set newname for datafile 8 to '/oradata/cxmtdb/pdbseed/undotbs01.dbf';
set newname for datafile 9 to '/oradata/cxmtdb/undotbs02.dbf';
set newname for datafile 10 to '/oradata/cxmtdb/hbhe/system01.dbf';
set newname for datafile 11 to '/oradata/cxmtdb/hbhe/sysaux01.dbf';
set newname for datafile 12 to '/oradata/cxmtdb/hbhe/undotbs01.dbf';
set newname for datafile 13 to '/oradata/cxmtdb/hbhe/undo_2.dbf';
set newname for datafile 14 to '/oradata/cxmtdb/hbhe/users01.dbf';
set newname for datafile 15 to '/oradata/cxmtdb/tbs01.dbf';
restore database;
switch datafile all;
switch datafile all;
recover database;
}11.恢复完数据库是mount状态；
查看日志文件并重命名到新的路径：https://www.cndba.cn/hbhe0316/article/4859
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/CXMTDB/ONLINELOG/group_2.263.1083760353
+DATA/CXMTDB/ONLINELOG/group_1.262.1083760353
+DATA/CXMTDB/ONLINELOG/group_3.270.1083761735
+DATA/CXMTDB/ONLINELOG/group_4.271.1083761743
SQL> alter database rename file '+DATA/CXMTDB/ONLINELOG/group_1.262.1083760353' to '/oradata/cxmtdb/redo01a.log';
Database altered.
SQL> alter database rename file '+DATA/CXMTDB/ONLINELOG/group_2.263.1083760353' to '/oradata/cxmtdb/redo02a.log';
Database altered.
SQL> alter database rename file '+DATA/CXMTDB/ONLINELOG/group_3.270.1083761735' to '/oradata/cxmtdb/redo03a.log';
Database altered.
SQL> alter database rename file '+DATA/CXMTDB/ONLINELOG/group_4.271.1083761743' to '/oradata/cxmtdb/redo04a.log';
Database altered.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/cxmtdb/redo02a.log
/oradata/cxmtdb/redo01a.log
/oradata/cxmtdb/redo03a.log
/oradata/cxmtdb/redo04a.log12.以resetlogs开库：https://www.cndba.cn/hbhe0316/article/4859
SQL> alter database open resetlogs;
Database altered.13.开库后的操作，查看数据文件：
https://www.cndba.cn/hbhe0316/article/4859
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/cxmtdb/system01.dbf
/oradata/cxmtdb/sysaux01.dbf
/oradata/cxmtdb/undotbs01.dbf
/oradata/cxmtdb/pdbseed/system01.dbf
/oradata/cxmtdb/pdbseed/sysaux01.dbf
/oradata/cxmtdb/users01.dbf
/oradata/cxmtdb/pdbseed/undotbs01.dbf
/oradata/cxmtdb/undotbs02.dbf
/oradata/cxmtdb/hbhe/system01.dbf
/oradata/cxmtdb/hbhe/sysaux01.dbf
/oradata/cxmtdb/hbhe/undotbs01.dbf
NAME
--------------------------------------------------------------------------------
/oradata/cxmtdb/hbhe/undo_2.dbf
/oradata/cxmtdb/hbhe/users01.dbf
/oradata/cxmtdb/tbs01.dbf
14 rows selected.14.查看控制文件：
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata/cxmtdb/control01.ctl
/oradata/cxmtdb/control02.ctl15.删除group 2https://www.cndba.cn/hbhe0316/article/4859
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/cxmtdb/redo02a.log
/oradata/cxmtdb/redo01a.log
/oradata/cxmtdb/redo03a.log
/oradata/cxmtdb/redo04a.log
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- ------------------ ------------------------
1 OPEN PUBLIC
2 CLOSED PUBLIC
禁用thread# 2
SQL> select group# from v$log where THREAD#=2;
GROUP#
----------
3
4
SQL> alter database disable thread 2;
Database altered.
删除thread 2的3 4号日志组：
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
查看日志组状态：
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- ------------------ ------------------------
1 OPEN PUBLIC16.清理undotbs2表空间：https://www.cndba.cn/hbhe0316/article/4859
SQL> sho parameter undo;
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
temp_undo_enabled boolean
FALSE
undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS1
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME
--------------------------------------------------------------------------------
UNDOTBS1
UNDOTBS217.重建temp表空间：
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
--------------------------------------------------------------------------------
TEMP
SQL> create temporary tablespace temp1 tempfile '/oradata/cxmtdb/temp01.dbf' size 1024m autoextend on;
Tablespace created.
设置默认表空间
SQL> alter database default temporary tablespace temp1;
Database altered.
删除旧的temp表空间：
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.18.重建密码文件：
[oracle@db01 tmp]$ orapwd file=orapwprodcdb password=S@34eapt
SQL> alter user sys identified by wwwwww container=all;
User altered.
SQL> alter user system identified by wwwwww container=all;
User altered.19.配置tnsnames.ora
[root@db01 admin]# pwd
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin
[root@db01 admin]# cat tnsnames.ora
CXMTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cxmtdb)
)
)
HBHE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hbhe)
)
)20.删除pdb的temp表空间https://www.cndba.cn/hbhe0316/article/4859https://www.cndba.cn/hbhe0316/article/4859
[oracle@db01 backup]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 4 15:03:52 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> alter pluggable database hbhe open;
SQL> alter session set container=hbhe;
Session altered.
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
--------------------------------------------------------------------------------
TEMP
SQL> create temporary tablespace temp1 tempfile '/oradata/cxmtdb/hbhe/temp01.dbf' size 1024m autoextend on;
Tablespace created.
SQL> alter database default temporary tablespace temp1;
Database altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.版权声明：本文为博主原创文章，未经博主允许不得转载。
Linux,oracle
984
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
