一、准备
在源库执行sql，得到run{…………}，rman时使用，确认异机环境存在目录：/data/app/oracle/oradata/orcl/ 可以修改
select 'run {'
from dual
union all
select 'set newname for datafile ' || file# || ' to ' ||
'''/data/app/oracle/oradata/orcl/' ||
decode(con_id, 2, 'pdbseed/', 3, 'srmprodb/', 4, 'itfprodb/', '') ||
lower(substr(name, instr(name, '/', -1) + 1)) || ''';'
from v$datafile
union all
select 'set newname for tempfile ' || file# || ' to ' ||
'''/data/app/oracle/oradata/orcl/' ||
decode(con_id, 2, 'pdbseed/', 3, 'srmprodb/', 4, 'itfprodb/', '') ||
lower(substr(name, instr(name, '/', -1) + 1)) || ''';'
from v$tempfile
union all
select 'alter database rename file ''' || MEMBER || ''' to ' ||
'''/data/app/oracle/oradata/orcl/' ||
lower(substr(MEMBER, instr(MEMBER, '/', -1) + 1)) || ''';'
from v$logfile
where type = 'ONLINE'
union all
select 'alter database clear logfile group ' || group# || ';'
from v$log
union all
select 'restore database;'
from dual
union all
select 'switch datafile all;'
from dual
union all
select 'switch tempfile all;'
from dual
union all
select 'recover database;'
from dual
union all
select '}'
from dual;二、执行
- 1.生成pfile并关闭当前数据库
恢复管理器完成。
[oracle@SRM-UAT-DB ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on 星期三 11月 29 19:26:00 2023
Copyright (c) 1982, 2016, Oracle. All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> create pfile='/tmp/pfile1.ora' from spfile;
SQL> shutdown immediate;
SQL> exit;- 2.修改/tmp/pfile1.ora内容
源机查询
select value from v$parameter where name='db_name';*.db_name=’XXX’
- 3.重置下环境变量
源机查询
select REGEXP_REPLACE(instance_name,'[0-9]', '') from v$instance;
结果：XXX
[oracle@SRM-UAT-DB ~]$ export ORACLE_SID=XXX- 4.用pfile启动数据库到nomount状态
[oracle@SRM-UAT-DB ~]$ sqlplus / as sysdba
SQL> startup nomount pfile='/tmp/pfile1.ora';
ORACLE 例程已经启动。
SQL> exit;- 5.rman下恢复控制文件并创建恢复目录
c-xxxxxxxx-xxxx 为控制文件名
[oracle@SRM-UAT-DB controlfile]$ rman target /
恢复管理器: Release 12.2.0.1.0 - Production on 星期三 11月 29 19:37:39 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: SRM (未装载)
RMAN> restore controlfile from '/data/backup/20230827/backup/controlfile/c-2023243544-20230827-00';可能出现错误
RMAN> sql 'alter database mount';
sql 语句: alter database mount
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: 位于 11/29/2023 19:38:11 的 default 通道上的 sql 命令失败
RMAN-11003: 在分析/执行 SQL 语句期间失败: alter database mount
ORA-00058: DB_BLOCK_SIZE 必须为 32768 才可装载此数据库 (非 8192)ORA-00058: DB_BLOCK_SIZE 必须为 32768 才可装载此数据库 (非 8192) 处理：
修改/tmp/pfile1.ora：*.db_block_size=32768
重启；
再次执行，报错
RMAN> sql 'alter database mount';
sql 语句: alter database mount
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: 位于 11/29/2023 19:45:32 的 default 通道上的 sql 命令失败
RMAN-11003: 在分析/执行 SQL 语句期间失败: alter database mount
ORA-65093: 多租户容器数据库设置不正确需要修改参数 enable_pluggable_database 为TRUE，同时新建spfile切换为spfile启动
[oracle@SRM-UAT-DB controlfile]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on 星期三 11月 29 19:53:05 2023
Copyright (c) 1982, 2016, Oracle. All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> create spfile from pfile='/tmp/pfile1.ora';
文件已创建。
SQL> show parameter enable_pluggable_database;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_pluggable_database boolean FALSE
SQL> shutdown immediate;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 4999610368 bytes
Fixed Size 8630952 bytes
Variable Size 1191185752 bytes
Database Buffers 3791650816 bytes
Redo Buffers 8142848 bytes
SQL> alter system set enable_pluggable_database=true scope=spfile;
系统已更改。
SQL> shutdown immediate;
ORA-01507: ??????
ORACLE 例程已经关闭。
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 4999610368 bytes
Fixed Size 8803024 bytes
Variable Size 1191185712 bytes
Database Buffers 3791650816 bytes
Redo Buffers 7970816 bytes
SQL> show parameter enable_pluggable_database;
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
enable_pluggable_database boolean
TRUE
SQL> exit再次执行重新执行rman
[oracle@SRM-UAT-DB controlfile]$ rman target /
恢复管理器: Release 12.2.0.1.0 - Production on 星期三 11月 29 19:57:52 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: SRM (未装载)
RMAN> restore controlfile from '/data/backup/20230827/backup/controlfile/c-2023243544-20230827-00';
从位于 2023-11-29 19:58:02 的 restore 开始
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=621 设备类型=DISK
通道 ORA_DISK_1: 正在还原控制文件
通道 ORA_DISK_1: 还原完成, 用时: 00:00:01
输出文件名=/data/app/oracle/oradata/orcl/control01.ctl
输出文件名=/data/app/oracle/oradata/orcl/control02.ctl
在 2023-11-29 19:58:03 完成了 restore
RMAN> sql 'alter database mount';
sql 语句: alter database mount
释放的通道: ORA_DISK_1
RMAN> catalog start with '/data/backup/20230827/backup/datafile/' noprompt;
搜索与样式 /data/backup/20230827/backup/datafile/ 匹配的所有文件
数据库未知文件的列表
=====================================
文件名: /data/backup/20230827/backup/datafile/20230827_level0_4648_1_1145935990.bkp
文件名: /data/backup/20230827/backup/datafile/c-2023243544-20230827-00
文件名: /data/backup/20230827/backup/datafile/20230827_level0_4646_1_1145933169.bkp
文件名: /data/backup/20230827/backup/datafile/20230827_level0_4647_1_1145935835.bkp
文件名: /data/backup/20230827/backup/datafile/20230827_level0_4649_1_1145936027.log
文件名: /data/backup/20230827/backup/datafile/20230827_level0_4645_1_1145926943.bkp
正在编制文件目录...
目录编制完毕
已列入目录的文件的列表
=======================
文件名: /data/backup/20230827/backup/datafile/20230827_level0_4648_1_1145935990.bkp
文件名: /data/backup/20230827/backup/datafile/c-2023243544-20230827-00
文件名: /data/backup/20230827/backup/datafile/20230827_level0_4646_1_1145933169.bkp
文件名: /data/backup/20230827/backup/datafile/20230827_level0_4647_1_1145935835.bkp
文件名: /data/backup/20230827/backup/datafile/20230827_level0_4649_1_1145936027.log
文件名: /data/backup/20230827/backup/datafile/20230827_level0_4645_1_1145926943.bkp
# run脚本在最上面通过SQL从源服务器获取
RMAN> run {
set newname for datafile 1 to '/data/app/oracle/oradata/orcl/system01.dbf';
set newname for datafile 2 to '/data/app/oracle/oradata/orcl/pdbseed/system01.dbf';
……
……
……
}
漫长的等待
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
……
正在执行命令: SET NEWNAME
已处理语句
已处理语句
……
已处理语句
………
……………
……
通道 ORA_DISK_1: 读取段句柄 /rman/mdm/backup/archivelog/20230827_level0_3971_1_1145933160.log 时发现错误
通道 ORA_DISK_1: 故障转移到片段句柄 = /data/backup/20230827/backup/datafile/20230827_level0_3971_1_1145933160.log 标记 = TAG20230827T024600
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:03
归档日志文件名 = /data/app/oracle/product/12.2.0/db_1/dbs/arch1_38103_987030938.dbf 线程 = 1 序列 = 38103
归档日志文件名 = /data/app/oracle/product/12.2.0/db_1/dbs/arch2_9775_987030938.dbf 线程 = 2 序列 = 9775
无法找到归档日志
归档日志线程 = 1 序列 = 38104
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: 位于 11/29/2023 17:34:56 的 recover 命令失败
RMAN-06054: 介质恢复正在请求未知的线程 1 序列 38104 的归档日志以及起始 SCN 11637401087
RMAN> exitSCN 错误可忽略
[oracle@MDM-UAT-DB orcl]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on 星期三 11月 29 17:38:55 2023
Copyright (c) 1982, 2016, Oracle. All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter database open resetlogs;
PDB切换(PDB模式下需要)
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPRODB READ WRITE NO
SQL> alter session set container=MYPRODB;
Session altered.
SQL> shutdown immmediate;
SQL> startup;
其他
- 常用命令
select value from v$parameter where name='db_name';
select REGEXP_REPLACE(instance_name,'[0-9]', '') from v$instance;
export ORACLE_SID=SRM
ps aux |grep pmon
lsnrctl status
du -sh *- sqlplus 操作
sqlplus / as sysdba
startup nomount pfile='/tmp/pfile1.ora';
shutdown immediate;
create spfile from pfile='/tmp/pfile1.ora';
alter system set enable_pluggable_database=true scope=spfile;
show parameter enable_pluggable_database;
show pdbs;
alter session set container=MDMPRODB;
alter user srm identified by mdm2023;
select username from dba_users;rman恢复进度
SELECT sid,
serial#,
CONTEXT,
message,
sofar,
totalwork,
round(sofar / totalwork * 100,2) "%_COMPLETE"
FROM gv$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork;
97
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
