Oracle RAC异机恢复到单机步骤及常见问题-duidaima 堆代码

Oracle RAC异机恢复到单机步骤及常见问题

发布于 2个月前
 469 热度

 0 评论

人生如流水
0 粉丝 47 篇博客

环境说明:
源库: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
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
SQL> exit

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;

报错:
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 18024 and starting SCN of 375681013
详细信息如下:

......
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文件:

SQL> select file_name from dba_temp_files;
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
// 堆代码 duidaima.com
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> shutdown immediate
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.

 用户评论

Oracle技术
 51 成员 |  32 话题
+我要提问 +随便写写

可能感兴趣的话题

如何收缩Oracle中的高水位？

网传超600万条Oracle Cloud认证用户数据被售卖

如何模拟和解决Oracel数据库的ORA-600 [4193]错误？

Oracle中如何通过BBED强制打开数据库实现数据恢复