ASM的备份集在文件系统上恢复测试
2017-09-03 10:03 AlfredZhao 阅读(1080) 评论(0) 编辑 收藏 举报背景：最近时常有客户咨询这类问题，其实很简单一个操作，但由于每个人的理解差异，也容易出现各种问题或者误解，本文主要总结下这个过程以及常遇到的问题处理。
环境：Site A(Oracle RAC 11.2.0.4) + Site B(Single Instance DB 11.2.0.4，只安装了DB软件)
需求：使用Site A的备份，恢复数据到Site B环境。
1.备份数据库和控制文件
一般使用ASM的场景是RAC，当然也有单机使用ASM的环境（Standalone）。这些都不重要，即使是使用的文件系统，备份数据库的方法也都是一样的。 首先备份Site A的数据库，然后备份Site A最新的控制文件，示例如下：--备份数据库：
RMAN> backup as compressed backupset database format '/public/hotback/primary_db_%d_%T_%s.bak';
--备份控制文件：
RMAN> backup current controlfile format '/public/hotback/primary_ctl.bak';
2.恢复数据库
现在我们有了Site A的数据库和控制文件的备份文件，传输到Site B端，使得Site B可以有权限看到这些备份集。 我这里的备份集是在我的NAS存储上，可以在Site B直接看到这些备份集。我们想一下，目前Site B是只有11.2.0.4的数据库软件，没有任何库，现在需要将Site A的备份导入到Site B中，那么我们至少需要有参数文件，控制文件，数据文件。
- 参数文件，可以考虑从Site A中备份一个并修改，也可以自己手工写一个，目的是把数据库先启动到nomount状态；
- 控制文件，在数据库已经nomount的基础上，根据备份集恢复出控制文件，并确认可以mount数据库；
- 数据文件，在数据库已经mount的基础上，设定要恢复数据文件的目录，根据备份集恢复数据库，最终确认可以open数据库。
思路就是这么简单，但是实际做的时候，我们来看下具体会有哪些常见的问题和误解，导致操作遇到问题。
恢复数据库大体步骤：
--1.参数文件，nomount
vi /tmp/pfile.ora11204
db_name=jingyu
sqlplus / as sysdba
startup nomount pfile='/tmp/pfile.ora11204';
--2.控制文件，mount
rman target /
restore controlfile from '/public/hotback/primary_ctl.bak';
alter database mount;
--3.数据文件，open
run {
set newname for database to '/u01/oradata/%U';
restore database;
switch database to copy;
}
- 常见问题1:数据库mount报错ORA-00201
- 常见问题2:数据库mount报错ORA-01103
- 常见问题3:数据库mount成功后数据文件的路径是ASM磁盘的路径
- 常见问题4:数据库open不成功
常见问题1:数据库mount报错ORA-00201
现象如下：RMAN> alter database mount;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/01/2017 11:07:34
ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version 11.2.0.0.0
ORA-00202: control file: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/cntrljingyu.dbf'
这个问题报错提示很明显，就是说控制文件的版本11.2.0.4.0 和Oracle的版本11.2.0.0.0 不兼容，解决也很简单，在手工创建的参数文件中加入参数：
compatible=11.2.0.4
这样就可以解决这个报错。
常见问题2:数据库mount报错ORA-01103
现象如下：RMAN> alter database mount;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/01/2017 11:18:14
ORA-01103: database name 'JYZHAO' in control file is not 'JINGYU'
这个问题更明显，是数据库的db_name定义有问题，和要恢复的数据库的db_name不一致，只需要修正参数文件的db_name参数即可：
db_name=jyzhao
这样就可以解决这个报错。
常见问题3:数据库mount成功后数据文件的路径是ASM磁盘的路径
现象如下：SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA1/jyzhao/datafile/system.256.919998779
+DATA1/jyzhao/datafile/sysaux.257.919998781
+DATA1/jyzhao/datafile/undotbs1.258.919998783
+DATA1/jyzhao/datafile/users.259.919998789
+DATA1/jyzhao/datafile/undotbs2.264.919999419
+DATA1/jyzhao/datafile/dbs_d_jingyu.268.947072261
+DATA1/jyzhao/datafile/dbs_i_jingyu.270.947072263
+DATA1/jyzhao/datafile/test.271.947072293
+DATA1/jyzhao/datafile/test2.272.947072883
+DATA1/jyzhao/datafile/dbadata.275.952933837
10 rows selected.
这本来是天经地义的一件事，因为之前的数据库是在ASM环境，控制文件也是之前环境备份出来的，记录的肯定是ASM的路径无疑。
但是很多新手这里由于理解不到位，不理解，甚至直接继续去尝试从备份集中restore数据库，那自然会报错，以为这些ASM的路径，在新的环境根本就不存在。
那么正确的做法是，规划好新的环境存放数据文件的目录，分配好权限，然后恢复前需要使用set newname来指定新的目录，注意是在run块中操作：
--很多时候需要将备份集catalog进新的环境，我这里由于是环境特殊，路径没变，所以不需要。
--catalog start with '/public/hotback/';
--restore数据库
run {
set newname for database to '/u01/oradata/%U';
restore database;
}
--切换数据库到copy备份
switch database to copy;
实际操作过程如下：
RMAN> run {
2> set newname for database to '/u01/oradata/%U';
3> restore database;
4> }
executing command: SET NEWNAME
Starting restore at 2017-09-01 11:24:54
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/data_D-JYZHAO_TS-SYSTEM_FNO-1
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/data_D-JYZHAO_TS-SYSAUX_FNO-2
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/data_D-JYZHAO_TS-UNDOTBS1_FNO-3
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/data_D-JYZHAO_TS-USERS_FNO-4
channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/data_D-JYZHAO_TS-UNDOTBS2_FNO-5
channel ORA_DISK_1: restoring datafile 00006 to /u01/oradata/data_D-JYZHAO_TS-DBS_D_JINGYU_FNO-6
channel ORA_DISK_1: restoring datafile 00007 to /u01/oradata/data_D-JYZHAO_TS-DBS_I_JINGYU_FNO-7
channel ORA_DISK_1: restoring datafile 00008 to /u01/oradata/data_D-JYZHAO_TS-TEST_FNO-8
channel ORA_DISK_1: restoring datafile 00009 to /u01/oradata/data_D-JYZHAO_TS-TEST2_FNO-9
channel ORA_DISK_1: restoring datafile 00011 to /u01/oradata/data_D-JYZHAO_TS-DBADATA_FNO-11
channel ORA_DISK_1: reading from backup piece /public/hotback/primary_db_JYZHAO_20170901_246.bak
channel ORA_DISK_1: piece handle=/public/hotback/primary_db_JYZHAO_20170901_246.bak tag=TAG20170901T225631
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:15:15
Finished restore at 2017-09-01 11:40:10
RMAN> list copy of database;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - ------------------- ---------- -------------------
8 1 A 2017-09-01 11:32:57 16152838 2017-09-01 22:56:31
Name: /u01/oradata/data_D-JYZHAO_TS-SYSTEM_FNO-1
9 2 A 2017-09-01 11:36:01 16152838 2017-09-01 22:56:31
Name: /u01/oradata/data_D-JYZHAO_TS-SYSAUX_FNO-2
7 3 A 2017-09-01 11:26:30 16152838 2017-09-01 22:56:31
Name: /u01/oradata/data_D-JYZHAO_TS-UNDOTBS1_FNO-3
4 4 A 2017-09-01 11:25:27 16152838 2017-09-01 22:56:31
Name: /u01/oradata/data_D-JYZHAO_TS-USERS_FNO-4
6 5 A 2017-09-01 11:26:14 16152838 2017-09-01 22:56:31
Name: /u01/oradata/data_D-JYZHAO_TS-UNDOTBS2_FNO-5
5 6 A 2017-09-01 11:25:32 16152838 2017-09-01 22:56:31
Name: /u01/oradata/data_D-JYZHAO_TS-DBS_D_JINGYU_FNO-6
2 7 A 2017-09-01 11:25:17 16152838 2017-09-01 22:56:31
Name: /u01/oradata/data_D-JYZHAO_TS-DBS_I_JINGYU_FNO-7
3 8 A 2017-09-01 11:25:20 16152838 2017-09-01 22:56:31
Name: /u01/oradata/data_D-JYZHAO_TS-TEST_FNO-8
1 9 A 2017-09-01 11:24:57 16152838 2017-09-01 22:56:31
Name: /u01/oradata/data_D-JYZHAO_TS-TEST2_FNO-9
10 11 A 2017-09-01 11:38:09 16152838 2017-09-01 22:56:31
Name: /u01/oradata/data_D-JYZHAO_TS-DBADATA_FNO-11
RMAN> switch database to copy;
datafile 1 switched to datafile copy "/u01/oradata/data_D-JYZHAO_TS-SYSTEM_FNO-1"
datafile 2 switched to datafile copy "/u01/oradata/data_D-JYZHAO_TS-SYSAUX_FNO-2"
datafile 3 switched to datafile copy "/u01/oradata/data_D-JYZHAO_TS-UNDOTBS1_FNO-3"
datafile 4 switched to datafile copy "/u01/oradata/data_D-JYZHAO_TS-USERS_FNO-4"
datafile 5 switched to datafile copy "/u01/oradata/data_D-JYZHAO_TS-UNDOTBS2_FNO-5"
datafile 6 switched to datafile copy "/u01/oradata/data_D-JYZHAO_TS-DBS_D_JINGYU_FNO-6"
datafile 7 switched to datafile copy "/u01/oradata/data_D-JYZHAO_TS-DBS_I_JINGYU_FNO-7"
datafile 8 switched to datafile copy "/u01/oradata/data_D-JYZHAO_TS-TEST_FNO-8"
datafile 9 switched to datafile copy "/u01/oradata/data_D-JYZHAO_TS-TEST2_FNO-9"
datafile 11 switched to datafile copy "/u01/oradata/data_D-JYZHAO_TS-DBADATA_FNO-11"
RMAN> list copy of database;
specification does not match any datafile copy in the repository
RMAN>
switch database to copy前后，查询v$datafile的内容对比如下：
SQL> /
NAME
--------------------------------------------------------------------------------
+DATA1/jyzhao/datafile/system.256.919998779
+DATA1/jyzhao/datafile/sysaux.257.919998781
+DATA1/jyzhao/datafile/undotbs1.258.919998783
+DATA1/jyzhao/datafile/users.259.919998789
+DATA1/jyzhao/datafile/undotbs2.264.919999419
+DATA1/jyzhao/datafile/dbs_d_jingyu.268.947072261
+DATA1/jyzhao/datafile/dbs_i_jingyu.270.947072263
+DATA1/jyzhao/datafile/test.271.947072293
+DATA1/jyzhao/datafile/test2.272.947072883
+DATA1/jyzhao/datafile/dbadata.275.952933837
10 rows selected.
SQL> /
NAME
--------------------------------------------------------------------------------
/u01/oradata/data_D-JYZHAO_TS-SYSTEM_FNO-1
/u01/oradata/data_D-JYZHAO_TS-SYSAUX_FNO-2
/u01/oradata/data_D-JYZHAO_TS-UNDOTBS1_FNO-3
/u01/oradata/data_D-JYZHAO_TS-USERS_FNO-4
/u01/oradata/data_D-JYZHAO_TS-UNDOTBS2_FNO-5
/u01/oradata/data_D-JYZHAO_TS-DBS_D_JINGYU_FNO-6
/u01/oradata/data_D-JYZHAO_TS-DBS_I_JINGYU_FNO-7
/u01/oradata/data_D-JYZHAO_TS-TEST_FNO-8
/u01/oradata/data_D-JYZHAO_TS-TEST2_FNO-9
/u01/oradata/data_D-JYZHAO_TS-DBADATA_FNO-11
10 rows selected.
常见问题4:数据库open不成功
查询控制文件和数据文件头记录的检查点信息：col checkpoint_change# for 999999999999
select checkpoint_change# from v$database;
select checkpoint_change# from v$datafile;
select checkpoint_change# from v$datafile_header;
查询结果如下：
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
16113160
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
16152838
16152838
16152838
16152838
16152838
16152838
16152838
16152838
16152838
16152838
10 rows selected.
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
16152838
16152838
16152838
16152838
16152838
16152838
16152838
16152838
16152838
16152838
10 rows selected.
SQL> select current_scn||'' from v$database;
CURRENT_SCN||''
----------------------------------------
0
此时数据库直接open肯定是不成功的，会报错如下：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
因为之前的备份只有数据库，没有完整的归档，最终做的是不完全恢复，开库必须要指定resetlogs，在尝试resetlogs之前，我们来看下redo的信息：
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
2 ONLINE +DATA1/jyzhao/onlinelog/group_2.262.919999045 NO
2 ONLINE +FRA1/jyzhao/onlinelog/group_2.258.919999049 YES
1 ONLINE +DATA1/jyzhao/onlinelog/group_1.261.919999039 NO
1 ONLINE +FRA1/jyzhao/onlinelog/group_1.257.919999041 YES
3 ONLINE +DATA1/jyzhao/onlinelog/group_3.265.919999607 NO
3 ONLINE +FRA1/jyzhao/onlinelog/group_3.259.919999611 YES
4 ONLINE +DATA1/jyzhao/onlinelog/group_4.266.919999613 NO
4 ONLINE +FRA1/jyzhao/onlinelog/group_4.260.919999615 YES
11 STANDBY +FRA1/jyzhao/standbylog/standby_group_11.log NO
12 STANDBY +FRA1/jyzhao/standbylog/standby_group_12.log NO
13 STANDBY +FRA1/jyzhao/standbylog/standby_group_13.log NO
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
21 STANDBY +FRA1/jyzhao/standbylog/standby_group_21.log NO
22 STANDBY +FRA1/jyzhao/standbylog/standby_group_22.log NO
23 STANDBY +FRA1/jyzhao/standbylog/standby_group_23.log NO
14 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1 1 1094 52428800 512 2 YES INACTIVE 16128216 2017-09-01 22:02:05 16136911 2017-09-01 22:12:09
2 1 1095 52428800 512 2 NO CURRENT 16136911 2017-09-01 22:12:09 2.8147E+14
3 2 872 52428800 512 2 NO CURRENT 16113160 2017-09-01 21:32:38 2.8147E+14
4 2 871 52428800 512 2 YES INACTIVE 16073483 2017-09-01 20:42:15 16113160 2017-09-01 21:32:38
这些redo日志路径还定义在ASM磁盘组上，显然是不合我们要求的，所以我们需要在新的文件系统路径下创建新的redo日志文件，并删除ASM的那些日志文件，同时我们现在的环境是单实例，不再需要thread 2：
alter database add logfile group 5 '/u01/oradata/redo01.log' size 50M;
alter database add logfile group 6 '/u01/oradata/redo02.log' size 50M;
alter database add logfile group 7 '/u01/oradata/redo03.log' size 50M;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
alter database drop logfile group 21;
alter database drop logfile group 22;
alter database drop logfile group 23;
alter database disable thread 2;
实际操作发现下面这些操作无法成功,报错主要是ORA-01623，ORA-01567，ORA-01109：
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance jyzhao (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '+DATA1/jyzhao/onlinelog/group_2.262.919999045'
ORA-00312: online log 2 thread 1: '+FRA1/jyzhao/onlinelog/group_2.258.919999049'
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance jyzhao2 (thread 2) - cannot drop
ORA-00312: online log 3 thread 2: '+DATA1/jyzhao/onlinelog/group_3.265.919999607'
ORA-00312: online log 3 thread 2: '+FRA1/jyzhao/onlinelog/group_3.259.919999611'
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01567: dropping log 4 would leave less than 2 log files for instance jyzhao2 (thread 2)
ORA-00312: online log 4 thread 2: '+DATA1/jyzhao/onlinelog/group_4.266.919999613'
ORA-00312: online log 4 thread 2: '+FRA1/jyzhao/onlinelog/group_4.260.919999615'
SQL> alter database disable thread 2;
alter database disable thread 2
*
ERROR at line 1:
ORA-01109: database not open
其实这些报错提示都特别明显，要么是当前日志不能删除，要么是不能少于2组日志，要么是数据库没有open。
此时的现状是：
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
2 ONLINE +DATA1/jyzhao/onlinelog/group_2.262.919999045 NO
2 ONLINE +FRA1/jyzhao/onlinelog/group_2.258.919999049 YES
3 ONLINE +DATA1/jyzhao/onlinelog/group_3.265.919999607 NO
3 ONLINE +FRA1/jyzhao/onlinelog/group_3.259.919999611 YES
4 ONLINE +DATA1/jyzhao/onlinelog/group_4.266.919999613 NO
4 ONLINE +FRA1/jyzhao/onlinelog/group_4.260.919999615 YES
5 ONLINE /u01/oradata/redo01.log NO
6 ONLINE /u01/oradata/redo02.log NO
7 ONLINE /u01/oradata/redo03.log NO
9 rows selected.
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
2 INVALID ONLINE +data1 NO
2 INVALID ONLINE +fra1 YES
3 INVALID ONLINE +data1 NO
3 INVALID ONLINE +fra1 YES
4 INVALID ONLINE +data1 NO
4 INVALID ONLINE +fra1 YES
5 ONLINE /u01/oradata/redo01.log NO
6 ONLINE /u01/oradata/redo02.log NO
7 ONLINE /u01/oradata/redo03.log NO
9 rows selected.
SQL> SELECT * FROM V$LOG;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
6 1 0 52428800 512 1 YES UNUSED 0 0
2 1 0 52428800 512 2 NO CLEARING_CURRENT 16136911 2017-09-01 22:12:09 2.8147E+14
7 1 0 52428800 512 1 YES UNUSED 0 0
5 1 0 52428800 512 1 YES UNUSED 0 0
3 2 0 52428800 512 2 NO CLEARING_CURRENT 16113160 2017-09-01 21:32:38 2.8147E+14
4 2 0 52428800 512 2 YES CLEARING 16073483 2017-09-01 20:42:15 16113160 2017-09-01 21:32:38
6 rows selected.
利用重建控制文件删除那些无用的redo路径：
SQL> alter database backup controlfile to trace as '/tmp/control.bak0901';
Database altered.
SQL> create spfile from pfile='/tmp/pfile.ora11204';
File created.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JYZHAO" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 1168
LOGFILE
GROUP 5 '/u01/oradata/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 6 '/u01/oradata/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 7 '/u01/oradata/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/oradata/data_D-JYZHAO_TS-SYSTEM_FNO-1',
'/u01/oradata/data_D-JYZHAO_TS-SYSAUX_FNO-2',
'/u01/oradata/data_D-JYZHAO_TS-UNDOTBS1_FNO-3',
'/u01/oradata/data_D-JYZHAO_TS-USERS_FNO-4',
'/u01/oradata/data_D-JYZHAO_TS-UNDOTBS2_FNO-5',
'/u01/oradata/data_D-JYZHAO_TS-DBS_D_JINGYU_FNO-6',
'/u01/oradata/data_D-JYZHAO_TS-DBS_I_JINGYU_FNO-7',
'/u01/oradata/data_D-JYZHAO_TS-TEST_FNO-8',
'/u01/oradata/data_D-JYZHAO_TS-TEST2_FNO-9',
'/u01/oradata/data_D-JYZHAO_TS-DBADATA_FNO-11'
CHARACTER SET ZHS16GBK
;
CREATE CONTROLFILE REUSE DATABASE "JYZHAO" NORESETLOGS FORCE LOGGING ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01192: must have at least one enabled thread
报错ORA-01503，ORA-01192。
根据报错，再次重建控制文件（注意关键字变化），创建成功：
CREATE CONTROLFILE set DATABASE "JYZHAO" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 1168
LOGFILE
GROUP 5 '/u01/oradata/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 6 '/u01/oradata/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 7 '/u01/oradata/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/oradata/data_D-JYZHAO_TS-SYSTEM_FNO-1',
'/u01/oradata/data_D-JYZHAO_TS-SYSAUX_FNO-2',
'/u01/oradata/data_D-JYZHAO_TS-UNDOTBS1_FNO-3',
'/u01/oradata/data_D-JYZHAO_TS-USERS_FNO-4',
'/u01/oradata/data_D-JYZHAO_TS-UNDOTBS2_FNO-5',
'/u01/oradata/data_D-JYZHAO_TS-DBS_D_JINGYU_FNO-6',
'/u01/oradata/data_D-JYZHAO_TS-DBS_I_JINGYU_FNO-7',
'/u01/oradata/data_D-JYZHAO_TS-TEST_FNO-8',
'/u01/oradata/data_D-JYZHAO_TS-TEST2_FNO-9',
'/u01/oradata/data_D-JYZHAO_TS-DBADATA_FNO-11'
CHARACTER SET ZHS16GBK
;
创建成功.
再次尝试open数据库：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database 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
根据报错ORA-38856匹配到MOS文档：
RMAN Duplicate from RAC backup fails ORA-38856 (文档 ID 334899.1)
As a workaround you could do the following:
1.Set the following parameter in the auxiliary init.ora file:
_no_recovery_through_resetlogs=TRUE
Then open with resetlogs;
Once the auxiliary is opened, remove this hidden parameter.
也就是可以指定参数 _no_recovery_through_resetlogs=TRUE.然后resetlogs成功后再移除此参数，再重启数据库。
SQL> show parameter _no_recovery_through_resetlogs
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_no_recovery_through_resetlogs boolean TRUE
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
5 1 1 52428800 512 1 NO CURRENT 16152839 2017-09-01 12:14:36 2.8147E+14
6 1 0 52428800 512 1 YES UNUSED 0 0
7 1 0 52428800 512 1 YES UNUSED 0 0
SQL>
SQL>
SQL>
SQL> select * from v$Logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
7 ONLINE /u01/oradata/redo03.log NO
6 ONLINE /u01/oradata/redo02.log NO
5 ONLINE /u01/oradata/redo01.log NO
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 229683200 bytes
Fixed Size 2251936 bytes
Variable Size 171967328 bytes
Database Buffers 50331648 bytes
Redo Buffers 5132288 bytes
Database mounted.
Database opened.
SQL>
观察告警日志，看是否有报错警告提示信息之类，发现如下信息：
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
Empty temporary tablespace: TEMP_JINGYU
*********************************************************************
很简单，临时表空间没有具体的临时文件，添加即可：
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/temp01.dbf' size 30G;
ALTER TABLESPACE temp_jingyu ADD TEMPFILE '/u01/oradata/temp_jingyu01.dbf' size 30G;
本文完。
AlfredZhao©版权所有「从Oracle起航，领略精彩的IT技术。」
