环境说明:
源库:Oracle 11.2.0.4.0 RAC
目标库:Oracle 11.2.0.4.0 单机
操作系统:kylin v10 SP1
将源库备份文件拷贝到目标服务器:
恢复过程:
1.检查备份文件
2.准备环境变量
3.准备参数文件
4.启动实例
5.恢复控制文件
6.文件路径
7.注册备份文件所在目录
8.执行restore
9.执行recover
10.修改 redo 路径
11.open数据库
1.检查备份文件
oracle@cjc-db-01:/home/oracle$ls -lrht /db/oracle/bak/test/rman/
total 727M
-rw-r--r-- 1 oracle oinstall 54M Jun 17 00:45 rman_cjc_db_20240617_ok2thsoj_1_1.bak
-rw-r--r-- 1 oracle oinstall 1.3M Jun 17 00:45 rman_cjc_db_20240617_ol2thsp3_1_1.bak
-rw-r--r-- 1 oracle oinstall 96K Jun 17 00:45 rman_cjc_db_20240617_om2thsp5_1_1.bak
-rw-r--r-- 1 oracle oinstall 208M Jun 17 00:45 rman_cjc_db_20240617_oi2thsoj_1_1.bak
-rw-r--r-- 1 oracle oinstall 208M Jun 17 00:45 rman_cjc_db_20240617_oj2thsoj_1_1.bak
-rw-r--r-- 1 oracle oinstall 213M Jun 17 00:45 rman_cjc_db_20240617_oh2thsoj_1_1.bak
-rw-r--r-- 1 oracle oinstall 2.5K Jun 17 00:46 rman_cjc_arch_20240617_oq2thsq8_1_1.bak
-rw-r--r-- 1 oracle oinstall 6.5K Jun 17 00:46 rman_cjc_arch_20240617_on2thsq8_1_1.bak
-rw-r--r-- 1 oracle oinstall 2.7M Jun 17 00:46 rman_cjc_arch_20240617_op2thsq8_1_1.bak
-rw-r--r-- 1 oracle oinstall 23M Jun 17 00:46 rman_cjc_arch_20240617_oo2thsq8_1_1.bak
-rw-r--r-- 1 oracle oinstall 19M Jun 17 00:46 rman_cjc_ctl_bak_20240617_or2thsqc_1_1.bak
-rw-r--r-- 1 oracle oinstall 13K Jun 17 00:46 rman_cjc_20240617.log
-rw-r--r-- 1 oracle oinstall 52K Jun 17 00:47 backup.log
2.准备环境变量
oracle@cjc-db-01:/home/oracle$vi .bash_profile
# Source /root/.bashrc if user has one
[ -f ~/.bashrc ] && . ~/.bashrc
export ORACLE_BASE=/db/oracle/app/oracle
export ORACLE_HOME=/db/oracle/app/oracle/product/11.2/db
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_SID=cjc2
alias cdo='cd $ORACLE_HOME'
alias cdb='cd $ORACLE_HOME/dbs'
alias cdn='cd $ORACLE_HOME/network/admin'
alias cdal='cd $ORACLE_BASE/diag/rdbms/*/cjc2/trace'
alias sqp='sqlplus / as sysdba'
3.准备参数文件
oracle@cjc-db-01:/db/oracle/app/oracle/product/11.2/db/dbs$vi initcjc2.ora
*.compatible='11.2.0.4.0'
*.control_files='/db/oracle/oradata/cjc/control01.ctl'
*.db_block_size=8192
*.db_name='cjc'
*.diagnostic_dest='/db/oracle/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=1593835520
*.processes=150
*.sga_target=4783603712
*.undo_tablespace='UNDOTBS1'
#*.log_archive_dest_1='location=/db/oracle/arch'
#*.log_archive_format='cjc_%t_%s_%r.arc'
4.启动实例
SQL> startup nomount
5.恢复控制文件
rman target /
RMAN> restore controlfile from '/db/oracle/bak/test/rman/rman_cjc_ctl_bak_20240617_or2thsqc_1_1.bak';
挂载数据库
RMAN> alter database mount;
6.文件路径
oracle@cjc-db-01:/home/oracle$sqlplus / as sysdba
set line 300
set pagesize 100;
col name for a60
select * from v$dbfile;
FILE# NAME
---------- ------------------------------------------------------------
1 +DATA/cjc/datafile/system.259.1038241499
2 +DATA/cjc/datafile/sysaux.260.1038241501
3 +DATA/cjc/datafile/undotbs1.261.1038241503
4 +DATA/cjc/datafile/undotbs2.263.1038241509
5 +DATA/cjc/datafile/users.264.1038241509
6 +DATA/cjc/datafile/cjc.279.1038246921
7 +DATA/cjc/datafile/cjc_ind.280.1038246923
8 +DATA/cjc/datafile/cjc.281.1038246945
9 +DATA/cjc/datafile/cjc.282.1038246949
10 +DATA/cjc/datafile/cjc.283.1038246959
11 +DATA/cjc/datafile/cjc.284.1038246961
12 +DATA/cjc/datafile/cjc_ind.285.1038246995
13 +DATA/cjc/datafile/xxx.381.1062437923
13 rows selected
临时文件:
select name from v$tempfile;
NAME
------------------------------------------------------------
+DATA/cjc/tempfile/temp.262.1038241505
日志文件
SET LINE 300
col MEMBER FOR A55
SELECT GROUP#,MEMBER FROM V$LOGFILE ORDER BY 1;
GROUP# MEMBER
---------- -------------------------------------------------------
1 +DATA/cjc/onlinelog/rlog_1_2_512m
1 +DATA/cjc/onlinelog/group_1.257.1038241495
2 +DATA/cjc/onlinelog/group_2.258.1038241497
2 +DATA/cjc/onlinelog/rlog_2_2_512m
3 +DATA/cjc/onlinelog/group_3.265.1038241847
3 +DATA/cjc/onlinelog/rlog_3_2_512m
4 +DATA/cjc/onlinelog/group_4.266.1038241849
4 +DATA/cjc/onlinelog/rlog_4_2_512m
5 +DATA/cjc/onlinelog/rlog_5_1_512m
5 +DATA/cjc/onlinelog/rlog_5_2_512m
6 +DATA/cjc/onlinelog/rlog_6_1_512m
6 +DATA/cjc/onlinelog/rlog_6_2_512m
12 rows selected.
批量生成 SET NEWNAME …语句
将 +DATA/cjc/datafile 转换为 /db/oracle/oradata/cjc
set line 300
set pagesize 100
col new_datafile for a100
select 'SET NEWNAME FOR DATAFILE '||file#||' to '''||replace(name, '+DATA/cjc/datafile', '/db/oracle/oradata/cjc')||''';' as new_datafile from v$datafile order by file#;
NEW_DATAFILE
----------------------------------------------------------------------------------------------------
SET NEWNAME FOR DATAFILE 1 to '/db/oracle/oradata/cjc/system.259.1038241499';
SET NEWNAME FOR DATAFILE 2 to '/db/oracle/oradata/cjc/sysaux.260.1038241501';
SET NEWNAME FOR DATAFILE 3 to '/db/oracle/oradata/cjc/undotbs1.261.1038241503';
SET NEWNAME FOR DATAFILE 4 to '/db/oracle/oradata/cjc/undotbs2.263.1038241509';
SET NEWNAME FOR DATAFILE 5 to '/db/oracle/oradata/cjc/users.264.1038241509';
SET NEWNAME FOR DATAFILE 6 to '/db/oracle/oradata/cjc/cjc.279.1038246921';
SET NEWNAME FOR DATAFILE 7 to '/db/oracle/oradata/cjc/cjc_ind.280.1038246923';
SET NEWNAME FOR DATAFILE 8 to '/db/oracle/oradata/cjc/cjc.281.1038246945';
SET NEWNAME FOR DATAFILE 9 to '/db/oracle/oradata/cjc/cjc.282.1038246949';
SET NEWNAME FOR DATAFILE 10 to '/db/oracle/oradata/cjc/cjc.283.1038246959';
SET NEWNAME FOR DATAFILE 11 to '/db/oracle/oradata/cjc/cjc.284.1038246961';
SET NEWNAME FOR DATAFILE 12 to '/db/oracle/oradata/cjc/cjc_ind.285.1038246995';
SET NEWNAME FOR DATAFILE 13 to '/db/oracle/oradata/cjc/xxx.381.1062437923';
13 rows selected.
set line 300
set pagesize 100
col new_tempfile for a100
select 'SET NEWNAME FOR TEMPFILE '||file#||' to '''||replace(name, '+DATA/cjc/tempfile', '/db/oracle/oradata/cjc')||''';' as new_tempfile from v$tempfile order by file#;
NEW_TEMPFILE
----------------------------------------------------------------------------------------------------
SET NEWNAME FOR TEMPFILE 1 to '/db/oracle/oradata/cjc/temp.262.1038241505';
实例是mount状态，不支持查询dba_data_file表、dba_temp_files表
ORA-01219: database not open: queries allowed on fixed tables/views only
7.注册备份文件所在目录
RMAN> catalog start with '/db/oracle/bak/test/rman/'
开始恢复
8.执行restore
rman target /
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
SET NEWNAME FOR DATAFILE 1 to '/db/oracle/oradata/cjc/system.259.1038241499';
SET NEWNAME FOR DATAFILE 2 to '/db/oracle/oradata/cjc/sysaux.260.1038241501';
SET NEWNAME FOR DATAFILE 3 to '/db/oracle/oradata/cjc/undotbs1.261.1038241503';
SET NEWNAME FOR DATAFILE 4 to '/db/oracle/oradata/cjc/undotbs2.263.1038241509';
SET NEWNAME FOR DATAFILE 5 to '/db/oracle/oradata/cjc/users.264.1038241509';
SET NEWNAME FOR DATAFILE 6 to '/db/oracle/oradata/cjc/cjc.279.1038246921';
SET NEWNAME FOR DATAFILE 7 to '/db/oracle/oradata/cjc/cjc_ind.280.1038246923';
SET NEWNAME FOR DATAFILE 8 to '/db/oracle/oradata/cjc/cjc.281.1038246945';
SET NEWNAME FOR DATAFILE 9 to '/db/oracle/oradata/cjc/cjc.282.1038246949';
SET NEWNAME FOR DATAFILE 10 to '/db/oracle/oradata/cjc/cjc.283.1038246959';
SET NEWNAME FOR DATAFILE 11 to '/db/oracle/oradata/cjc/cjc.284.1038246961';
SET NEWNAME FOR DATAFILE 12 to '/db/oracle/oradata/cjc/cjc_ind.285.1038246995';
SET NEWNAME FOR DATAFILE 13 to '/db/oracle/oradata/cjc/xxx.381.1062437923';
SET NEWNAME FOR TEMPFILE 1 to '/db/oracle/oradata/cjc/temp.262.1038241505';
restore database;
switch datafile all;
switch tempfile all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}
检查restore的文件
oracle@cjc-db-01:/db/oracle/app/oracle/diag/rdbms/cjc/cjc2/trace$ls -lrth /db/oracle/oradata/cjc/
total 11G
-rw-r----- 1 oracle oinstall 11M Jun 18 14:13 xxx.381.1062437923
-rw-r----- 1 oracle oinstall 5.1M Jun 18 14:13 users.264.1038241509
-rw-r----- 1 oracle oinstall 306M Jun 18 14:13 undotbs1.261.1038241503
-rw-r----- 1 oracle oinstall 1.6G Jun 18 14:13 undotbs2.263.1038241509
-rw-r----- 1 oracle oinstall 1.1G Jun 18 14:13 cjc_ind.280.1038246923
-rw-r----- 1 oracle oinstall 1.1G Jun 18 14:13 cjc_ind.285.1038246995
-rw-r----- 1 oracle oinstall 701M Jun 18 14:13 system.259.1038241499
-rw-r----- 1 oracle oinstall 631M Jun 18 14:13 sysaux.260.1038241501
-rw-r----- 1 oracle oinstall 1.1G Jun 18 14:14 cjc.279.1038246921
-rw-r----- 1 oracle oinstall 1.1G Jun 18 14:14 cjc.282.1038246949
-rw-r----- 1 oracle oinstall 1.1G Jun 18 14:14 cjc.283.1038246959
-rw-r----- 1 oracle oinstall 1.1G Jun 18 14:14 cjc.284.1038246961
-rw-r----- 1 oracle oinstall 1.1G Jun 18 14:14 cjc.281.1038246945
-rw-r----- 1 oracle oinstall 19M Jun 18 14:15 control01.ctl
9.执行recover
执行recover前，先检查下其他信息:
查看备份信息:
oracle@cjc-db-01:/home/oracle$export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
oracle@cjc-db-01:/home/oracle$rman target /
RMAN> list backup of archivelog all summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
28012 B A A SBT_TAPE 2024-05-31 23:01:06 1 1 NO 2024_05_31_01_01_1
......
28431 B A A SBT_TAPE 2024-06-16 23:01:06 1 1 NO 2024_06_16_01_01_33
28439 B A A DISK 2024-06-17 00:46:00 1 2 YES TAG20240617T004600
28440 B A A DISK 2024-06-17 00:46:00 1 2 YES TAG20240617T004600
28441 B A A DISK 2024-06-17 00:46:01 1 2 YES TAG20240617T004600
28442 B A A DISK 2024-06-17 00:46:03 1 2 YES TAG20240617T004600
查看控制文件记录的归档信息,最后一个归档SEQUENCE#是18023，NEXT_CHANGE#是375681013。
SET LINE 300
SET PAGESIZE 500
COL NAME FOR A70
SELECT THREAD#,SEQUENCE#,NAME,FIRST_CHANGE#,NEXT_CHANGE#,TO_CHAR(FIRST_TIME,'YYYY-MM-DD HH24:MI:SS') AS FIRST_TIME,TO_CHAR(NEXT_TIME,'YYYY-MM-DD HH24:MI:SS') AS NEXT_TIME FROM V$ARCHIVED_LOG WHERE NAME IS NOT NULL ORDER BY 1,2;
THREAD# SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE# FIRST_TIME NEXT_TIME
---------- ---------- ---------------------------------------------------------------------- ------------- ------------ ------------------- -------------------
1 18079 +DATA/cjc/archivelog/2024_06_16/thread_1_seq_18079.488.1171839657 375651813 375651830 2024-06-16 23:00:48 2024-06-16 23:00:57
1 18080 +DATA/cjc/archivelog/2024_06_17/thread_1_seq_18080.503.1171845953 375651830 375681000 2024-06-16 23:00:57 2024-06-17 00:45:51
1 18081 +DATA/cjc/archivelog/2024_06_17/thread_1_seq_18081.388.1171845959 375681000 375681017 2024-06-17 00:45:51 2024-06-17 00:45:58
2 18021 +DATA/cjc/archivelog/2024_06_16/thread_2_seq_18021.462.1171839657 375651810 375651827 2024-06-16 23:00:48 2024-06-16 23:00:57
2 18022 +DATA/cjc/archivelog/2024_06_17/thread_2_seq_18022.428.1171845953 375651827 375681003 2024-06-16 23:00:57 2024-06-17 00:45:52
2 18023 +DATA/cjc/archivelog/2024_06_17/thread_2_seq_18023.403.1171845955 375681003 375681013 2024-06-17 00:45:52 2024-06-17 00:45:55
6 rows selected.
查看日志信息
select thread#,group#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,status from v$log order by 1,4;
THREAD# GROUP# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# STATUS
---------- ---------- ---------- ------------- ------------ ----------------
1 2 18080 375651830 375681000 ACTIVE
1 5 18081 375681000 375681017 ACTIVE
1 1 18082 375681017 2.8147E+14 CURRENT
2 3 18022 375651827 375681003 ACTIVE
2 4 18023 375681003 375681013 ACTIVE
2 6 18024 375681013 2.8147E+14 CURRENT
6 rows selected.
查看v$datafile
set pagesize 100
set line 300
col name for a55
select FILE#,NAME,CHECKPOINT_CHANGE#,TO_CHAR(CHECKPOINT_TIME,'YYYY-MM-DD HH24:MI:SS') CHECKPOINT_TIME from v$datafile;
FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------------------------------------------- ------------------ -------------------
1 /db/oracle/oradata/cjc/system.259.1038241499 375680301 2024-06-17 00:45:08
2 /db/oracle/oradata/cjc/sysaux.260.1038241501 375680297 2024-06-17 00:45:07
3 /db/oracle/oradata/cjc/undotbs1.261.1038241503 375680299 2024-06-17 00:45:07
4 /db/oracle/oradata/cjc/undotbs2.263.1038241509 375680301 2024-06-17 00:45:08
5 /db/oracle/oradata/cjc/users.264.1038241509 375680295 2024-06-17 00:45:07
6 /db/oracle/oradata/cjc/cjc.279.1038246921 375680299 2024-06-17 00:45:07
7 /db/oracle/oradata/cjc/cjc_ind.280.1038246923 375680297 2024-06-17 00:45:07
8 /db/oracle/oradata/cjc/cjc.281.1038246945 375680295 2024-06-17 00:45:07
9 /db/oracle/oradata/cjc/cjc.282.1038246949 375680299 2024-06-17 00:45:07
10 /db/oracle/oradata/cjc/cjc.283.1038246959 375680297 2024-06-17 00:45:07
11 /db/oracle/oradata/cjc/cjc.284.1038246961 375680295 2024-06-17 00:45:07
12 /db/oracle/oradata/cjc/cjc_ind.285.1038246995 375680295 2024-06-17 00:45:07
13 /db/oracle/oradata/cjc/xxx.381.1062437923 375680297 2024-06-17 00:45:07
13 rows selected.
查看 V$DATAFILE_HEADER
set pagesize 100
set line 300
col name for a60
SELECT FILE#,NAME,CREATION_CHANGE#,CHECKPOINT_CHANGE#,TO_CHAR(CHECKPOINT_TIME,'YYYY-MM-DD HH24:MI:SS') CHECKPOINT_TIME FROM V$DATAFILE_HEADER;
FILE# NAME CREATION_CHANGE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------------------------------------------------ ---------------- ------------------ -------------------
1 /db/oracle/oradata/cjc/system.259.1038241499 7 375680301 2024-06-17 00:45:08
2 /db/oracle/oradata/cjc/sysaux.260.1038241501 1717 375680297 2024-06-17 00:45:07
3 /db/oracle/oradata/cjc/undotbs1.261.1038241503 2679 375680299 2024-06-17 00:45:07
4 /db/oracle/oradata/cjc/undotbs2.263.1038241509 14019 375680301 2024-06-17 00:45:08
5 /db/oracle/oradata/cjc/users.264.1038241509 14338 375680295 2024-06-17 00:45:07
6 /db/oracle/oradata/cjc/cjc.279.1038246921 225813 375680299 2024-06-17 00:45:07
7 /db/oracle/oradata/cjc/cjc_ind.280.1038246923 226260 375680297 2024-06-17 00:45:07
8 /db/oracle/oradata/cjc/cjc.281.1038246945 226675 375680295 2024-06-17 00:45:07
9 /db/oracle/oradata/cjc/cjc.282.1038246949 227083 375680299 2024-06-17 00:45:07
10 /db/oracle/oradata/cjc/cjc.283.1038246959 227479 375680297 2024-06-17 00:45:07
11 /db/oracle/oradata/cjc/cjc.284.1038246961 227873 375680295 2024-06-17 00:45:07
12 /db/oracle/oradata/cjc/cjc_ind.285.1038246995 228292 375680295 2024-06-17 00:45:07
13 /db/oracle/oradata/cjc/xxx.381.1062437923 23251485 375680297 2024-06-17 00:45:07
13 rows selected.
###recover 数据库
RMAN> recover database;
......
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/db/oracle/app/oracle/product/11.2/db/dbs/arch2_18022_1038241491.dbf thread=2 sequence=18022
archived log file name=/db/oracle/app/oracle/product/11.2/db/dbs/arch1_18080_1038241491.dbf thread=1 sequence=18080
archived log file name=/db/oracle/app/oracle/product/11.2/db/dbs/arch1_18081_1038241491.dbf thread=1 sequence=18081
archived log file name=/db/oracle/app/oracle/product/11.2/db/dbs/arch2_18023_1038241491.dbf thread=2 sequence=18023
unable to find archived log
archived log thread=2 sequence=18024
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/18/2024 14:54:14
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 18024 and starting SCN of 375681013
之前查到，控制文件中记录的最后一个归档是18023，没有18024。
再次执行recover:
RMAN> recover database until scn 375681013;
Starting recover at 2024-06-18 14:58:04
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2024-06-18 14:58:05
10.修改 redo 路径
查看路径:
set pagesize 100
select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/cjc/onlinelog/group_1.257.1038241495
+DATA/cjc/onlinelog/group_2.258.1038241497
+DATA/cjc/onlinelog/group_3.265.1038241847
+DATA/cjc/onlinelog/group_4.266.1038241849
+DATA/cjc/onlinelog/rlog_5_1_512m
+DATA/cjc/onlinelog/rlog_5_2_512m
+DATA/cjc/onlinelog/rlog_6_1_512m
+DATA/cjc/onlinelog/rlog_6_2_512m
+DATA/cjc/onlinelog/rlog_1_2_512m
+DATA/cjc/onlinelog/rlog_2_2_512m
+DATA/cjc/onlinelog/rlog_3_2_512m
+DATA/cjc/onlinelog/rlog_4_2_512m
12 rows selected.
执行修改:
alter database rename file '+DATA/cjc/onlinelog/rlog_3_2_512m' to '/db/oracle/oradata/cjc/rlog_3_2_512m';
alter database rename file '+DATA/cjc/onlinelog/rlog_4_2_512m' to '/db/oracle/oradata/cjc/rlog_4_2_512m';
alter database rename file '+DATA/cjc/onlinelog/rlog_2_2_512m' to '/db/oracle/oradata/cjc/rlog_2_2_512m';
alter database rename file '+DATA/cjc/onlinelog/rlog_1_2_512m' to '/db/oracle/oradata/cjc/rlog_1_2_512m';
alter database rename file '+DATA/cjc/onlinelog/rlog_6_2_512m' to '/db/oracle/oradata/cjc/rlog_6_2_512m';
alter database rename file '+DATA/cjc/onlinelog/rlog_6_1_512m' to '/db/oracle/oradata/cjc/rlog_6_1_512m';
alter database rename file '+DATA/cjc/onlinelog/rlog_5_2_512m' to '/db/oracle/oradata/cjc/rlog_5_2_512m';
alter database rename file '+DATA/cjc/onlinelog/rlog_5_1_512m' to '/db/oracle/oradata/cjc/rlog_5_1_512m';
alter database rename file '+DATA/cjc/onlinelog/group_4.266.1038241849' to '/db/oracle/oradata/cjc/group_4.266.1038241849';
alter database rename file '+DATA/cjc/onlinelog/group_3.265.1038241847' to '/db/oracle/oradata/cjc/group_3.265.1038241847';
alter database rename file '+DATA/cjc/onlinelog/group_2.258.1038241497' to '/db/oracle/oradata/cjc/group_2.258.1038241497';
alter database rename file '+DATA/cjc/onlinelog/group_1.257.1038241495' to '/db/oracle/oradata/cjc/group_1.257.1038241495';
验证:
set pagesize 100
select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/db/oracle/oradata/cjc/group_1.257.1038241495
/db/oracle/oradata/cjc/group_2.258.1038241497
/db/oracle/oradata/cjc/group_3.265.1038241847
/db/oracle/oradata/cjc/group_4.266.1038241849
/db/oracle/oradata/cjc/rlog_5_1_512m
/db/oracle/oradata/cjc/rlog_5_2_512m
/db/oracle/oradata/cjc/rlog_6_1_512m
/db/oracle/oradata/cjc/rlog_6_2_512m
/db/oracle/oradata/cjc/rlog_1_2_512m
/db/oracle/oradata/cjc/rlog_2_2_512m
/db/oracle/oradata/cjc/rlog_3_2_512m
/db/oracle/oradata/cjc/rlog_4_2_512m
12 rows selected.
11.open数据库
先只读打开
SQL> alter database open read only;
Database altered.
检查数据没问题以后，在读写打开
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open RESETLOGS;
检查其他文件路径:
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/db/oracle/oradata/cjc/temp.262.1038241505
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/db/oracle/oradata/cjc/system.259.1038241499
/db/oracle/oradata/cjc/sysaux.260.1038241501
/db/oracle/oradata/cjc/undotbs1.261.1038241503
/db/oracle/oradata/cjc/undotbs2.263.1038241509
/db/oracle/oradata/cjc/users.264.1038241509
/db/oracle/oradata/cjc/cjc.279.1038246921
/db/oracle/oradata/cjc/cjc_ind.280.1038246923
/db/oracle/oradata/cjc/cjc.281.1038246945
/db/oracle/oradata/cjc/cjc.282.1038246949
/db/oracle/oradata/cjc/cjc.283.1038246959
/db/oracle/oradata/cjc/cjc.284.1038246961
/db/oracle/oradata/cjc/cjc_ind.285.1038246995
/db/oracle/oradata/cjc/xxx.381.1062437923
13 rows selected.
异机恢复问题汇总：
问题一:recover错误
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 18024 and starting SCN of 375681013
报错：RMAN-06054
在11g官方文档error messages中对错误描述如下：
RMAN-06054: media recovery requesting unknown archived log for thread string with sequence string and starting SCN of string
Cause: Media recovery is requesting a log whose existence is not recorded in the recovery catalog or target database control file.
Action: If a copy of the log is available, then add it to the recovery catalog and/or control file via a CATALOG command and then retry the RECOVER command.
If not, then a point-in-time recovery up to the missing log is the only alternative and database can be opened using ALTER DATABASE OPEN RESETLOGS command.
redo 日志不可用，控制文件找不到 redo 日志作为结尾，所以会一直读归档，直至找不到，无关紧要的报错，可以查看 SCN 来确定是否一致。
可见，出先此错误的原因是恢复需要的日志记录在控制文件或恢复目录中找不到。解决方法分两种情况：
1.如果相关的日志存在且可用的话，就将此日志记录添加到控制文件或恢复目录中。
2.如果相关的日志已经被删除了或不可用了，那么就按照错误的提示scn将数据库恢复到此scn，本案例是375681013。也就是说此时数据库只能进行不完全恢复了，在打开数据库时得使用resetlogs打开。
解决方案:
再次执行recover:
RMAN> recover database until scn 375681013;
问题二:路径转换
源、目标数据文件、临时文件路径不同时，需要路径转换
数据文件示例:
SET NEWNAME FOR DATAFILE 13 to '/db/oracle/oradata/cjc/xxx.381.1062437923';
switch datafile all;
临时文件示例:
SET NEWNAME FOR TEMPFILE 1 to '/db/oracle/oradata/cjc/temp.262.1038241505';
switch tempfile all;
如果前面tempfile忘记路径转换了，后面需要手动添加tempfile文件:
no rows selected
SQL> select name from v$tempfile;
no rows selected
SQL> alter tablespace temp add tempfile '/db/oracle/oradata/ebeva/temp01.dbf' size 10M;
Tablespace altered.
问题三:日志文件路径无法修改
执行rename报错:ORA-01511，ORA-01516
SQL> alter database rename file '+DATA/ebeva/onlinelog/group_3.265.1038241847' to '/db/oracle/oradata/ebeva/group_3.265.1038241847';
alter database rename file '+DATA/ebeva/onlinelog/group_3.265.1038241847' to '/db/oracle/oradata/ebeva/group_3.265.1038241847'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file
"+DATA/ebeva/onlinelog/group_3.265.1038241847"
执行clear报错ORA-00349
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ORA-00349: failure obtaining block size for '+DATA/ebeva/onlinelog/rlog_2_2_512
oracle@SATEST-DB-004:/home/oracle$oerr ora 00349
00349, 00000, "failure obtaining block size for '%s'"
// *Cause: The operating system was unable to determine the blocksize
// for the given filename.
// *Action: Consult the accompanying error message, and correct the
// device or specify another filename.
解决方案:
添加新group
alter database add logfile group 7 '/db/oracle/oradata/ebeva/redo07.log' size 100M;
alter database add logfile group 8 '/db/oracle/oradata/ebeva/redo08.log' size 100M;
重建控制文件，去掉旧的group:
重建控制文件
alter database backup controlfile to trace as '/home/oracle/tmp/0618ctl.sql';
vi /home/oracle/tmp/0618.ctl
源文件:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "EBEVA" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+DATA/ebeva/onlinelog/rlog_1_2_512m',
'+data'
) SIZE 512M BLOCKSIZE 512,
GROUP 5 (
'+DATA/ebeva/onlinelog/rlog_5_1_512m',
'+DATA/ebeva/onlinelog/rlog_5_2_512m'
) SIZE 512M BLOCKSIZE 512
DATAFILE
'/db/oracle/oradata/ebeva/system.dbf',
'/db/oracle/oradata/ebeva/sysaux.dbf',
'/db/oracle/oradata/ebeva/undotbs1.dbf',
'/db/oracle/oradata/ebeva/undotbs2.dbf',
'/db/oracle/oradata/ebeva/users.dbf',
'/db/oracle/oradata/ebeva/ebeva01.dbf',
'/db/oracle/oradata/ebeva/ebeva_ind01.dbf',
'/db/oracle/oradata/ebeva/ebeva02.dbf',
'/db/oracle/oradata/ebeva/ebeva03.db',
'/db/oracle/oradata/ebeva/ebeva04.db',
'/db/oracle/oradata/ebeva/ebeva05.db',
'/db/oracle/oradata/ebeva/ebeva_ind02.dbf',
'/db/oracle/oradata/ebeva/daport.dbf'
CHARACTER SET AL32UTF8
;
LOGFILE部分改成:
GROUP 7 '/db/oracle/oradata/ebeva/redo07.log' SIZE 100M BLOCKSIZE 512 REUSE,
GROUP 8 '/db/oracle/oradata/ebeva/redo08.log' SIZE 100M BLOCKSIZE 512 REUSE;
修改后的文件:
vi /home/oracle/tmp/0618.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "EBEVA" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 7 '/db/oracle/oradata/ebeva/redo07.log' SIZE 100M BLOCKSIZE 512,
GROUP 8 '/db/oracle/oradata/ebeva/redo08.log' SIZE 100M BLOCKSIZE 512
DATAFILE
'/db/oracle/oradata/ebeva/system.dbf',
'/db/oracle/oradata/ebeva/sysaux.dbf',
'/db/oracle/oradata/ebeva/undotbs1.dbf',
'/db/oracle/oradata/ebeva/undotbs2.dbf',
'/db/oracle/oradata/ebeva/users.dbf',
'/db/oracle/oradata/ebeva/ebeva01.dbf',
'/db/oracle/oradata/ebeva/ebeva_ind01.dbf',
'/db/oracle/oradata/ebeva/ebeva02.dbf',
'/db/oracle/oradata/ebeva/ebeva03.db',
'/db/oracle/oradata/ebeva/ebeva04.db',
'/db/oracle/oradata/ebeva/ebeva05.db',
'/db/oracle/oradata/ebeva/ebeva_ind02.dbf',
'/db/oracle/oradata/ebeva/daport.dbf'
CHARACTER SET AL32UTF8
;
重建控制文件:
SQL> @/home/oracle/tmp/0618.ctl
ORACLE instance started.
Total System Global Area 4776853504 bytes
Fixed Size 2261288 bytes
Variable Size 922750680 bytes
Database Buffers 3841982464 bytes
Redo Buffers 9859072 bytes
Control file created.
问题四:open 报错
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
oracle@cjc-db-01:/home/oracle/tmp$oerr ora 38856
38856, 00000, "cannot mark instance %s (redo thread %s) as enabled"
// *Cause: The open resetlogs or standby activation operation failed because
// it needs to mark an instance (redo thread) as enabled. However, it
// had less than 2 online redo logs, which prevented it from being
// enabled.
// *Action: Add more logfiles to the specified instance and retry the command.
后台日志:
Tue Jun 18 11:22:43 2024
alter database open RESETLOGS
RESETLOGS after incomplete recovery UNTIL CHANGE 375681013
Clearing online redo logfile 7 /db/oracle/oradata/cjc/redo07.log
Clearing online log 7 of thread 1 sequence number 0
Errors in file /db/oracle/app/oracle/diag/rdbms/cjc/cjc2/trace/cjc2_ora_29264.trc:
ORA-00367: checksum error in log file header
ORA-00315: log 7 of thread 1, wrong thread # 2 in header
ORA-00312: online log 7 thread 1: '/db/oracle/oradata/cjc/redo07.log'
Clearing online redo logfile 7 complete
Clearing online redo logfile 8 /db/oracle/oradata/cjc/redo08.log
Clearing online log 8 of thread 1 sequence number 0
Errors in file /db/oracle/app/oracle/diag/rdbms/cjc/cjc2/trace/cjc2_ora_29264.trc:
ORA-00367: checksum error in log file header
ORA-00315: log 8 of thread 1, wrong thread # 2 in header
ORA-00312: online log 8 thread 1: '/db/oracle/oradata/cjc/redo08.log'
Clearing online redo logfile 8 complete
ORA-38856 signalled during: alter database open RESETLOGS...
解决方案:
添加THREAD 2 GROUP
SQL> select group#,THREAD#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
8 1 CURRENT
7 1 UNUSED
alter database add logfile THREAD 2 group 1 '/db/oracle/oradata/ebeva/redo01.log' size 100M;
alter database add logfile THREAD 2 group 2 '/db/oracle/oradata/ebeva/redo02.log' size 100M;
SQL> alter database open RESETLOGS;
Database altered.
###chenjuchao 20240620###
欢迎关注我的公众号《IT小Chen》
扫一扫
1986
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
