Oracle RMAN Migrate DB from RAC ASM to Single non-ASM

最新推荐文章于 2021-10-21 02:32:56 发布
最新推荐文章于 2021-10-21 02:32:56 发布
阅读量937 收藏
点赞数
分类专栏： Oracle Backup Recovery
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/deanza/article/details/9419549
版权

环境： Linux 5.5 x64 + Oracle 11.2.3 RAC + ASM ->Linux 5.5 x64 + Oracle 11.2.3 Single + non-ASM

迁移： ORALCE_HOME,DATABASE(pfile,controlfile,datafile,redolog,archivelog)

#######################################  RAC Oracle Home迁移成Single Oracle Home #######################################
1, Oracle Home从RAC拷贝到Single主机，启动SQLPLUS出现ORA-29702
http://somireddy.wordpress.com/2011/10/08/ora-29702-error-occurred-in-cluster-group-service-operation/
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk rac_off
make -f ins_rdbms.mk ioracle

[oraprod@q1ebsdb lib]$ ll|grep ins_rdbms.mk
-rw-r--r-- 1 oraprod oinstall   40678 Sep 17  2011 ins_rdbms.mk
[oraprod@q1ebsdb lib]$ make -f ins_rdbms.mk rac_off
rm -f /prod/oracle/product/11.2.0/db_1/lib/libskgxp11.so
cp /prod/oracle/product/11.2.0/db_1/lib//libskgxpg.so /prod/oracle/product/11.2.0/db_1/lib/libskgxp11.so
rm -f /prod/oracle/product/11.2.0/db_1/lib/libskgxn2.so
cp /prod/oracle/product/11.2.0/db_1/lib//libskgxns.so \
              /prod/oracle/product/11.2.0/db_1/lib/libskgxn2.so
/usr/bin/ar d /prod/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a kcsm.o
/usr/bin/ar cr /prod/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a /prod/oracle/product/11.2.0/db_1/rdbms/lib/ksnkcs.o

[oraprod@q1ebsdb lib]$ make -f ins_rdbms.mk ioracle
chmod 755 /prod/oracle/product/11.2.0/db_1/bin

 - Linking Oracle
rm -f /prod/oracle/product/11.2.0/db_1/rdbms/lib/oracle
gcc  -o /prod/oracle/product/11.2.0/db_1/rdbms/lib/oracle -m64 -L/prod/oracle/product/11.2.0/db_1/rdbms/lib/ -L/prod/oracle/product/11.2.0/db_1/lib/ -L/prod/oracle/product/11.2.0/db_1/lib/stubs/   -Wl,-E /prod/oracle/product/11.2.0/db_1/rdbms/lib/opimai.o /prod/oracle/product/11.2.0/db_1/rdbms/lib/ssoraed.o /prod/oracle/product/11.2.0/db_1/rdbms/lib/ttcsoi.o  -Wl,--whole-archive -lperfsrv11 -Wl,--no-whole-archive /prod/oracle/product/11.2.0/db_1/lib/nautab.o /prod/oracle/product/11.2.0/db_1/lib/naeet.o /prod/oracle/product/11.2.0/db_1/lib/naect.o /prod/oracle/product/11.2.0/db_1/lib/naedhs.o /prod/oracle/product/11.2.0/db_1/rdbms/lib/config.o  -lserver11 -lodm11 -lcell11 -lnnet11 -lskgxp11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11  -lvsn11 -lcommon11 -lgeneric11 -lknlopt `if /usr/bin/ar tv /prod/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap11" ; fi` -lslax11 -lpls11  -lrt -lplp11 -lserver11 -lclient11  -lvsn11 -lcommon11 -lgeneric11 `if [ -f /prod/oracle/product/11.2.0/db_1/lib/libavserver11.a ] ; then echo "-lavserver11" ; else echo "-lavstub11"; fi` `if [ -f /prod/oracle/product/11.2.0/db_1/lib/libavclient11.a ] ; then echo "-lavclient11" ; fi` -lknlopt -lslax11 -lpls11  -lrt -lplp11 -ljavavm11 -lserver11  -lwwg  `cat /prod/oracle/product/11.2.0/db_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /prod/oracle/product/11.2.0/db_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lztkg11 `cat /prod/oracle/product/11.2.0/db_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /prod/oracle/product/11.2.0/db_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `if /usr/bin/ar tv /prod/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo11"; fi` -L/prod/oracle/product/11.2.0/db_1/ctx/lib/ -lctxc11 -lctx11 -lzx11 -lgx11 -lctx11 -lzx11 -lgx11 -lordimt11 -lclsra11 -ldbcfg11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -locr11 -locrb11 -locrutl11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11  -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged  -lippsmerged -lippcore  -lippcpemerged -lippcpmerged  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lasmclnt11 -lcommon11 -lcore11 -laio    `cat /prod/oracle/product/11.2.0/db_1/lib/sysliblist` -Wl,-rpath,/prod/oracle/product/11.2.0/db_1/lib -lm    `cat /prod/oracle/product/11.2.0/db_1/lib/sysliblist` -ldl -lm   -L/prod/oracle/product/11.2.0/db_1/lib
test ! -f /prod/oracle/product/11.2.0/db_1/bin/oracle ||\
           mv -f /prod/oracle/product/11.2.0/db_1/bin/oracle /prod/oracle/product/11.2.0/db_1/bin/oracleO
mv /prod/oracle/product/11.2.0/db_1/rdbms/lib/oracle /prod/oracle/product/11.2.0/db_1/bin/oracle
chmod 6751 /prod/oracle/product/11.2.0/db_1/bin/oracle

#######################################  RMAN恢复备份集权限不足 #######################################

2, 问题2:备份集权限不足。
RMAN> restore spfile from '/u01/FULL_PROD_20130715_16';
Starting restore at 15-JUL-13
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/15/2013 17:32:26
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
这里是权限的问题，设置全部可读写，恢复成功。
[root@q1ebsdb u01]# chmod 777 ARCH_PROD_20130715_17 CTRL_PROD_20130715_18 FULL_PROD_20130715_1*
[oraprod@q1ebsdb u01]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 15 17:33:10 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PROD (not mounted)
RMAN> restore spfile from '/u01/FULL_PROD_20130715_16';
Starting restore at 15-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/FULL_PROD_20130715_16
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 15-JUL-13

#######################################  备份数据库 #######################################
#  备份数据库。
backup full database tag 'FULL' format='/u01/FULL_%d_%T_%s';
backup archivelog all tag 'ARCH' format '/u01/ARCH_%d_%T_%s' delete all input;
backup current controlfile tag 'CTRL'  format '/u01/CTRL_%d_%T_%s';

#######################################3种方式恢复数据库 RAC ASM ->Single non-ASM#######################################
1，相同DBNAME，相同DBID
2，不同DBNAME，相同DBID
3，不同DBNAME，不同DBID

####################################### 1 RMAN恢复相同DBNAME，相同DBID数据库 #######################################

#  恢复同名数据库，DBNAME和DBID都相同。
1，利用恢复出来的spfile生成pfile，并修改成initprod.ora，SQLPLUS启动数据库nomount状态。
2，恢复DB_NAME相同的数据库: RMAN恢复控制文件，转换恢复数据文件，恢复数据库，启用新的归档日志，再次恢复数据库。
startup nomount;
set dbid=228115897
restore controlfile from '/u01/CTRL_PROD_20130715_18';
alter database mount;
catalog start with '/u01';
run{     
SET NEWNAME FOR DATABASE TO '/prod/oracle/oradata/prod/%b';
SET NEWNAME FOR TEMPFILE 1 TO '/prod/oracle/oradata/prod/%b';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}
RECOVER DATABASE;
此时数据库会开始使用归档目录下的归档日志，重新catalog新产生并拷贝过来的归档文件。
catalog start with '/prod/oracle/fast_recovery_area/PROD/archivelog/2013_07_15';
RECOVER DATABASE;
alter database open resetlogs;
通过nis可以进行数据库改名。

#######################################  2 RMAN恢复不同DBNAME，但相同DBID数据库 #######################################

#  RMAN恢复到不同数据库，DBNAME不同，但DBID相同。
准备2个pfile，initprod.ora和initTEST.ora，仅仅db_name不同而已。
export ORACLE_SID=prod
startup nomount pfile='/prod/oracle/product/11.2.0/db_1/dbs/initprod.ora';
restore controlfile from '/u01/CTRL_PROD_20130715_18';
startup mount;
catalog start with '/u01/';
run{     
SET NEWNAME FOR DATABASE TO '/prod/oracle/oradata/TEST/%b';
SET NEWNAME FOR TEMPFILE 1 TO '/prod/oracle/oradata/TEST/%b';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}
修改logfile,主要是为了更新控制文件的在线日志从ASM->non-ASM。
alter database rename file '+DATADG/prod/onlinelog/group_1.267.817172147' to '/prod/oracle/oradata/TEST/group_1.267.817172147';
alter database rename file '+FRADG/prod/onlinelog/group_1.260.817172149' to '/prod/oracle/oradata/TEST/group_1.260.817172149';
alter database rename file '+DATADG/prod/onlinelog/group_2.266.817172151' to '/prod/oracle/oradata/TEST/group_2.266.817172151';
alter database rename file '+FRADG/prod/onlinelog/group_2.259.817172151' to '/prod/oracle/oradata/TEST/group_2.259.817172151';
alter database rename file '+DATADG/prod/onlinelog/group_3.265.817172153' to '/prod/oracle/oradata/TEST/group_3.265.817172153';
alter database rename file '+FRADG/prod/onlinelog/group_3.258.817172153' to '/prod/oracle/oradata/TEST/group_3.258.817172153';
alter database rename file '+DATADG/prod/onlinelog/group_4.264.817172155' to '/prod/oracle/oradata/TEST/group_4.264.817172155';
alter database rename file '+FRADG/prod/onlinelog/group_4.257.817172155' to '/prod/oracle/oradata/TEST/group_4.257.817172155';
alter database rename file '+DATADG/prod/onlinelog/group_5.271.817172163' to '/prod/oracle/oradata/TEST/group_5.271.817172163';
alter database rename file '+FRADG/prod/onlinelog/group_5.264.817172163' to '/prod/oracle/oradata/TEST/group_5.264.817172163';
alter database rename file '+DATADG/prod/onlinelog/group_6.270.817172163' to '/prod/oracle/oradata/TEST/group_6.270.817172163';
alter database rename file '+FRADG/prod/onlinelog/group_6.263.817172163' to '/prod/oracle/oradata/TEST/group_6.263.817172163';
alter database rename file '+DATADG/prod/onlinelog/group_7.269.817172167' to '/prod/oracle/oradata/TEST/group_7.269.817172167';
alter database rename file '+FRADG/prod/onlinelog/group_7.262.817172167' to '/prod/oracle/oradata/TEST/group_7.262.817172167';
alter database rename file '+DATADG/prod/onlinelog/group_8.268.817172167' to '/prod/oracle/oradata/TEST/group_8.268.817172167';
alter database rename file '+FRADG/prod/onlinelog/group_8.261.817172167' to '/prod/oracle/oradata/TEST/group_8.261.817172167';

SQL> alter database backup controlfile to trace as '/u01/control_trace.txt';--生成DBNAME=PROD的控制文件脚本。

shutdown immediate;

export ORACLE_SID=TEST
startup nomount pfile='/prod/oracle/product/11.2.0/db_1/dbs/initTEST.ora'

修改/u01/control_trace.txt，PROD=>TEST,利用NORETLOGS那一段脚本创建control_trace.sql,重建TEST控制文件。
SQL> !rm -rf /prod/oracle/oradata/TEST/control01.ctl
SQL> @/u01/control_trace.sql
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name PROD in file header does not match given name of TEST
ORA-01110: data file 1: '/prod/oracle/oradata/TEST/system.259.817171877'

修改control_trace.sql中RESET=>SET，注意这里是关键。
SQL> @/u01/control_trace.sql
CREATE CONTROLFILE SET DATABASE "TEST" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01223: RESETLOGS must be specified to set a new database name

修改control_trace.sql中NORESETLOGS=>RESETLOGS，注意这里也是关键。
SQL> @/u01/control_trace.sql

Control file created.

注意：这一步控制文件已经重建完毕，如果源数据库有结构变化，这里需要手动增加数据文件。这一步也可以放在后面恢复时提醒数据文件缺失并OFFLINE时进行处理。

RMAN恢复
export ORACLE_SID=TEST
RMAN> recover database;

Starting recover at 22-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

starting media recovery

unable to find archived log
archived log thread=1 sequence=369
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/22/2013 14:36:21
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 369 and starting SCN of 14781355

RMAN> catalog start with '/u01/'; --注意/u01/目录里只存放了恢复使用的备份集。
RMAN> recover database; --这里是进行归档日志的恢复，需要多尝试几次，它会利用所有的归档日志。
...
Executing: alter database datafile 6 offline
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_23/o1_mf_1_426_8yvs7zwz_.arc thread=1 sequence=426
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_23/o1_mf_2_179_8yvs7zw9_.arc thread=2 sequence=179
channel default: deleting archived log(s)
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_23/o1_mf_1_426_8yvs7zwz_.arc RECID=95 STAMP=821527552
archived log file name=/backup/thread_1_seq_427.364.821465293 thread=1 sequence=427
channel default: deleting archived log(s)
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_23/o1_mf_2_179_8yvs7zw9_.arc RECID=96 STAMP=821527552
archived log file name=/backup/thread_2_seq_180.368.821484023 thread=2 sequence=180
archived log file name=/backup/thread_1_seq_428.366.821473911 thread=1 sequence=428
archived log file name=/backup/thread_1_seq_429.361.821484021 thread=1 sequence=429
archived log file name=/backup/thread_1_seq_430.370.821491237 thread=1 sequence=430
archived log file name=/backup/thread_2_seq_181.369.821494839 thread=2 sequence=181
archived log file name=/backup/thread_1_seq_431.371.821502021 thread=1 sequence=431
unable to find archived log
archived log thread=2 sequence=182
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/23/2013 10:07:46
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 182 and starting SCN of 19195470

这里显示datafile 6没有，需要创建。
SQL> alter database create datafile 6 as '/prod/oracle/oradata/TEST/EBSDATA.ora';
SQL> alter database datafile 6 online;
RMAN> recover database; --此时会恢复数据文件6.

Starting recover at 23-JUL-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 427 is already on disk as file /backup/thread_1_seq_427.364.821465293
archived log for thread 1 with sequence 428 is already on disk as file /backup/thread_1_seq_428.366.821473911
archived log for thread 1 with sequence 429 is already on disk as file /backup/thread_1_seq_429.361.821484021
archived log for thread 1 with sequence 430 is already on disk as file /backup/thread_1_seq_430.370.821491237
archived log for thread 1 with sequence 431 is already on disk as file /backup/thread_1_seq_431.371.821502021
archived log for thread 1 with sequence 432 is already on disk as file /backup/thread_1_seq_432.373.821512819
archived log for thread 2 with sequence 180 is already on disk as file /backup/thread_2_seq_180.368.821484023
archived log for thread 2 with sequence 181 is already on disk as file /backup/thread_2_seq_181.369.821494839
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=179
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=426
channel ORA_DISK_1: reading from backup piece /backup/ARCH_PROD_20130722_20
channel ORA_DISK_1: piece handle=/backup/ARCH_PROD_20130722_20 tag=ARCH
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_23/o1_mf_1_426_8yvsh60n_.arc thread=1 sequence=426
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_23/o1_mf_2_179_8yvsh60j_.arc thread=2 sequence=179
channel default: deleting archived log(s)
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_23/o1_mf_1_426_8yvsh60n_.arc RECID=98 STAMP=821527782
archived log file name=/backup/thread_1_seq_427.364.821465293 thread=1 sequence=427
channel default: deleting archived log(s)
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_23/o1_mf_2_179_8yvsh60j_.arc RECID=99 STAMP=821527782
archived log file name=/backup/thread_2_seq_180.368.821484023 thread=2 sequence=180
archived log file name=/backup/thread_1_seq_428.366.821473911 thread=1 sequence=428
archived log file name=/backup/thread_1_seq_429.361.821484021 thread=1 sequence=429
archived log file name=/backup/thread_1_seq_430.370.821491237 thread=1 sequence=430
archived log file name=/backup/thread_2_seq_181.369.821494839 thread=2 sequence=181
archived log file name=/backup/thread_1_seq_431.371.821502021 thread=1 sequence=431
unable to find archived log
archived log thread=2 sequence=182
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/23/2013 10:09:45
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 182 and starting SCN of 19195470

RMAN> recover database; --最后一次恢复，会提示需要在线日志thread 2 sequence 182。

RMAN恢复会到最后一个归档日志应用完毕，接下来在SLQPLUS中恢复应用在线日志。
SQLPLUS恢复在线日志，下面的恢复正常完成。

SQL> recover database using backup controlfile;
ORA-00279: change 19195470 generated at 07/23/2013 01:00:39 needed for thread 2
ORA-00289: suggestion :
/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_23/o1_mf_2_182_%u_.arc
ORA-00280: change 19195470 for thread 2 is in sequence #182


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/backup/group_6.263.817172163
ORA-00279: change 19195470 generated at 07/23/2013 00:00:36 needed for thread 1
ORA-00289: suggestion : /backup/thread_1_seq_431.371.821502021
ORA-00280: change 19195470 for thread 1 is in sequence #431


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/backup/thread_1_seq_431.371.821502021
ORA-00279: change 19242558 generated at 07/23/2013 03:00:21 needed for thread 1
ORA-00289: suggestion : /backup/thread_1_seq_432.373.821512819
ORA-00280: change 19242558 for thread 1 is in sequence #432
ORA-00278: log file '/backup/thread_1_seq_431.371.821502021' no longer needed
for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/backup/thread_1_seq_432.373.821512819
ORA-00279: change 19312913 generated at 07/23/2013 06:00:17 needed for thread 1
ORA-00289: suggestion :
/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_23/o1_mf_1_433_%u_.arc
ORA-00280: change 19312913 for thread 1 is in sequence #433
ORA-00278: log file '/backup/thread_1_seq_432.373.821512819' no longer needed
for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/backup/group_2.259.817172151
Log applied.
Media recovery complete.
SQL> alter database open;

SQL> alter database open resetlogs;
Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/prod/oracle/oradata/TEST/TEMP.ora' size 16M autoextend on;

SQL>  select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDOTBS2
EBSDATA

SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/prod/oracle/oradata/TEST/undotbs2.256.817172295
/prod/oracle/oradata/TEST/users.257.817171937
/prod/oracle/oradata/TEST/undotbs1.258.817171901
/prod/oracle/oradata/TEST/sysaux.260.817171845
/prod/oracle/oradata/TEST/system.259.817171877
/prod/oracle/oradata/TEST/EBSDATA.ora

SQL> conn jeron_peng/amaxgs

        ID NAME
---------- ------------------------------------------------------------
         1 11.1.0.7.0
         2 11.2.0.3.0
         6 2013-07-15 23:42:00
         4 11.2.0.3.0Jul152013
         5 CompleteBackup
         3 11.2.0.3.0CPUApr2013
         7 2013-07-22 16:43:00
最后产生的数据已经恢复出来。

注意一点：这里其实数据库结构已经发生了变化，最新的控制文件已经创建了数据文件6，而恢复的控制文件是没有的，恢复时会提示数据文件OFFLINE，我们只需在后面创建并ONLINE。
SQL> alter database create datafile 6 as '/prod/oracle/oradata/TEST/EBSDATA.ora';
SQL> alter datafile 6 online;

#################################### 3 RMAN DUPLICATE恢复不同DBNAME，不同DBID数据库 #######################################

#  DUPLICATE恢复到不同名数据库。
Duplicate prod to TEST from backupset without target and catelog.
创建新库需要的audit目录，数据文件目录，trace文件目录，闪回恢复目录等。
创建密码文件，保持和源库完全一样。orapwd file=orapwTEST password=amaxgs
创建pfile，initTEST.ora，启动数据库，并创建spfile，重启数据库从spfile启动。
利用下面的Duplicate，注意BACKUP LOCATION路径组后有“/”，必须为‘/u01/’，否则‘/u01’会报错，找不到SPFILE或者CONTROLFILE等。
run{
SET NEWNAME FOR DATABASE TO '/prod/oracle/oradata/TEST/%b';
SET NEWNAME FOR TEMPFILE 1 TO '/prod/oracle/oradata/TEST/%b';
DUPLICATE DATABASE TO 'TEST' BACKUP LOCATION '/u01/';
}

[oratest@q1ebsdb ~]$ rman auxiliary /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jul 18 22:27:00 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: TEST (not mounted)

RMAN> exit


Recovery Manager complete.
[oratest@q1ebsdb ~]$ rman auxiliary /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jul 18 22:28:33 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: TEST (not mounted)

RMAN> run{
SET NEWNAME FOR DATABASE TO '/prod/oracle/oradata/TEST/%b';
2> 3> SET NEWNAME FOR TEMPFILE 1 TO '/prod/oracle/oradata/TEST/%b';
DUPLICATE DATABASE TO 'TEST' BACKUP LOCATION '/u01/';
}4> 5>

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 18-JUL-13

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''TEST'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/CTRL_PROD_20130715_18';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''TEST'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                494931288 bytes
Database Buffers             335544320 bytes
Redo Buffers                   6574080 bytes

Starting restore at 18-JUL-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/prod/oracle/oradata/TEST/control01.ctl
Finished restore at 18-JUL-13

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK

contents of Memory Script:
{
   set until scn  14784494;
   set newname for datafile  1 to
 "/prod/oracle/oradata/TEST/system.259.817171877";
   set newname for datafile  2 to
 "/prod/oracle/oradata/TEST/sysaux.260.817171845";
   set newname for datafile  3 to
 "/prod/oracle/oradata/TEST/undotbs1.258.817171901";
   set newname for datafile  4 to
 "/prod/oracle/oradata/TEST/users.257.817171937";
   set newname for datafile  5 to
 "/prod/oracle/oradata/TEST/undotbs2.256.817172295";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18-JUL-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /prod/oracle/oradata/TEST/system.259.817171877
channel ORA_AUX_DISK_1: restoring datafile 00002 to /prod/oracle/oradata/TEST/sysaux.260.817171845
channel ORA_AUX_DISK_1: restoring datafile 00003 to /prod/oracle/oradata/TEST/undotbs1.258.817171901
channel ORA_AUX_DISK_1: restoring datafile 00004 to /prod/oracle/oradata/TEST/users.257.817171937
channel ORA_AUX_DISK_1: restoring datafile 00005 to /prod/oracle/oradata/TEST/undotbs2.256.817172295
channel ORA_AUX_DISK_1: reading from backup piece /u01/FULL_PROD_20130715_15
channel ORA_AUX_DISK_1: piece handle=/u01/FULL_PROD_20130715_15 tag=FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 18-JUL-13

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=821140212 file name=/prod/oracle/oradata/TEST/system.259.817171877
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=821140212 file name=/prod/oracle/oradata/TEST/sysaux.260.817171845
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=821140212 file name=/prod/oracle/oradata/TEST/undotbs1.258.817171901
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=821140212 file name=/prod/oracle/oradata/TEST/users.257.817171937
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=821140212 file name=/prod/oracle/oradata/TEST/undotbs2.256.817172295

contents of Memory Script:
{
   set until scn  14784494;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 18-JUL-13
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=369
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=149
channel ORA_AUX_DISK_1: reading from backup piece /u01/ARCH_PROD_20130715_17
channel ORA_AUX_DISK_1: piece handle=/u01/ARCH_PROD_20130715_17 tag=ARCH
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_369_8yhz017z_.arc thread=1 sequence=369
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_18/o1_mf_2_149_8yhz01vo_.arc thread=2 sequence=149
channel clone_default: deleting archived log(s)
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_369_8yhz017z_.arc RECID=2 STAMP=821140226
channel clone_default: deleting archived log(s)
archived log file name=/prod/oracle/fast_recovery_area/TEST/archivelog/2013_07_18/o1_mf_2_149_8yhz01vo_.arc RECID=1 STAMP=821140226
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-JUL-13
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                494931288 bytes
Database Buffers             335544320 bytes
Redo Buffers                   6574080 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TEST'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''TEST'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                494931288 bytes
Database Buffers             335544320 bytes
Redo Buffers                   6574080 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1  SIZE 50 M ,
  GROUP   2  SIZE 50 M ,
  GROUP   3  SIZE 50 M ,
  GROUP   4  SIZE 50 M
 DATAFILE
  '/prod/oracle/oradata/TEST/system.259.817171877'
 CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE
 
 
 
  INSTANCE 'i2'
  GROUP   5  SIZE 50 M ,
  GROUP   6  SIZE 50 M ,
  GROUP   7  SIZE 50 M ,
  GROUP   8  SIZE 50 M

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/prod/oracle/oradata/TEST/temp.272.817172253";
   switch clone tempfile all;
   catalog clone datafilecopy  "/prod/oracle/oradata/TEST/sysaux.260.817171845",
 "/prod/oracle/oradata/TEST/undotbs1.258.817171901",
 "/prod/oracle/oradata/TEST/users.257.817171937",
 "/prod/oracle/oradata/TEST/undotbs2.256.817172295";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /prod/oracle/oradata/TEST/temp.272.817172253 in control file

cataloged datafile copy
datafile copy file name=/prod/oracle/oradata/TEST/sysaux.260.817171845 RECID=1 STAMP=821140247
cataloged datafile copy
datafile copy file name=/prod/oracle/oradata/TEST/undotbs1.258.817171901 RECID=2 STAMP=821140247
cataloged datafile copy
datafile copy file name=/prod/oracle/oradata/TEST/users.257.817171937 RECID=3 STAMP=821140247
cataloged datafile copy
datafile copy file name=/prod/oracle/oradata/TEST/undotbs2.256.817172295 RECID=4 STAMP=821140247

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=821140247 file name=/prod/oracle/oradata/TEST/sysaux.260.817171845
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=821140247 file name=/prod/oracle/oradata/TEST/undotbs1.258.817171901
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=821140247 file name=/prod/oracle/oradata/TEST/users.257.817171937
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=821140247 file name=/prod/oracle/oradata/TEST/undotbs2.256.817172295

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 18-JUL-13

################################# 注意事项 #################################
1，catalog start with '/u01/'; 目录必须为/u01/这种格式，/u01会出错。
2，创建控制文件RESET->SET，NORESETLOGS->RESETLOGS
3，DUPLICATE DATABASE TO 'TEST' BACKUP LOCATION '/u01/'; 目录必须为/u01/这种格式，/u01会出错。
deanza
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle EBS 基本业务流程图
hsghggt的博客
02-17 3257
Oracle EBS 基本业务流程图
Oracle19c rac备份数据通过rman恢复到单实例
最新发布
07-02
Oracle19c rac备份数据通过rman恢复到单实例
oracle 11G RAC +单机非ASM配置ADG
hzcyhujw的博客
10-16 1270
概述：rac配置ADG方法无不同，只是在备库上需要将参数文件的群集参数删除改成单机的就行 [grid@11g-rac1 ~]$ crsctl stat res -t -------------------------------------------------------------------------------- NAME           TARGET  STATE       ...
oracle期间平均成本,小李飞刀系列之Oracle EBS期间平均成本(PAC)--生产成本计算(四)制造费用设置及成本计算...
weixin_36072709的博客
04-05 1391
在上两篇文章中介绍了资源的设置及其成本计算，本篇文章将介绍与资源关联非常紧密的制造费用的设置及成本计算。1. 制造费用设置 图 1. 制造费用主设置界面制造费用主设置界面如图1所示，成本要素选择”制造费用”，并设置制造费用的名称、说明、吸收帐户、默认基本类型，这里”默认基本类型”的设置较关键。如图1所示，制造费用的”默认基本类型”共4种：物料、批次、资源单位、资源值。前两种与资源基本类型的含义一致...
今年一直搞Oracle EBS优化，脑壳痛
落落的专栏 专注SQL调优 性能调优
10-21 3469
搞了10几年的性能优化,OLTP系统,OLAP系统,Oracle,MYSQL,PG,Greenplum,Oceanbase,hive, 达梦等等各种数据库优化项目做过太多太多...唯独EBS系统没有单独做过优化，一直都很遗憾。 虽然之前做网络培训的时候教了几个EBS DBA徒弟，他们也找我优化过EBS的SQL，但是都是零零散散的。 今年开春，公司接了一个EBS优化项目(版本ebs11i)，这也算公司是第一个真正意义的EBS优化项目。 早期是3个DBA和3个EBS顾问以及项目经理在客户现场，搞了2个多月，进展
Oracle ERP DBA 主要工作职责
Vindak's DBA Life
08-05 2175
整理如下： 1. Oracle EBS 安装、配置、优化； 2. DEV 系统的Clone； 3. 做一般意义上Oracle DBA 的所有事情； 4. ERP 用户和权限管理； 5.ERP 大量的远程打印机管理； 6.做项目的时候，大量ERP 用户/权限/打印机/DB 原始数据的 批量导入； 7.ERP 和DB 优化； 8.开发人员的DBA 支持； 9.Patch Apply。
RMAN管理ORACLE-RAC数据库的备份与恢复
10-25
本文将详细介绍如何使用RMAN（Recovery Manager）来管理ORACLE RAC数据库的备份与恢复。 1. **修改 RAC 数据库的归档模式** 在进行RMAN备份时，数据库通常需要处于归档模式，这样可以确保在发生故障时有完整的事务...
Oracle单机通过rman迁移到RAC集群.docx
10-26
Oracle单机通过rman迁移到RAC集群
Oracle Data Guard RAC TO RAC迁移方案（基于rman备份）V1.1.pdf
09-24
本文档旨在提供一个基于 RMAN 备份的 Oracle Data Guard RAC TO RAC 迁移方案，以便帮助 DBA schnell 和可靠地完成数据库迁移任务。该方案适用 Oracle 11g 版本，文档版本为 1.0，发布日期为 2018 年 6 月。 第一章...
Oracle10g rac+asm 原库备份与恢复 .docx
07-21
总结，Oracle 10g RAC+ASM环境下的备份与恢复虽然在实现上与单实例有异同，但通过理解RAC的多实例共享和ASM的存储特性，以及熟练使用RMAN，可以有效地保护和恢复集群数据库。确保备份策略合理，定期进行备份测试，以...
Oracle EBS SLA 详解
月下狼~图腾~
11-20 1031
SLA概述          SLA :子分类账(Subledger Accounting)，这个在R12中大力宣扬的内容, 我们通常的认为总账就是对Journal的汇总, 但是在实际的操作中我们会发现, 对于Sub system 过来的Journal, 并不能单纯的以一种逻辑进行汇总, 比如 AP 传入到GL 的request，虽然有丰富的选项用来进行汇总, 但是对于是否要汇总Detail...
Oracle EBS Alert
Lumen专注Oracle EBS
02-02 1936
Alert 是一种Oracle系统中的一种机制，它可以监视系统数据库，在规定的情况下给规定用户一个通知，通知可以是邮件或者其他形式，在标注的系统和客户化系统中都是可以定义使用的。有两种类型的Alert: (1) Event Alert. 事件型的Alert会即时发出消息 (2) Periodic Alert. 周期型根据预定义周期检查数据库然后发出消息 Oracle Alert会在以上两种给
oracle ebs rac,安装Oracle 11i EBS+Database RAC,无论是对DBA ,还是做2次开发,都将对ORACLE EBS 的理解有很大的帮助...
weixin_42548816的博客
04-13 163
安装Oracle 11i EBS+Database RAC,无论是对DBA ,还是做2次开发,都将对ORACLE EBS 的理解有很大的帮助[@more@]此文档是相当相当资深的顾问写的,本人稍微做了一点修改拿出来和大家共享,希望能和大家一起进步,一起学习!无论是做ERP DBA 的同志,还是做ERP 2 次开发的,对ORACLE ERP 的理解,将都有很大的好处~+++++++++++++++主...
RAC+ADG(单节点ADG)
Baldwin的博客
03-31 6646
RAC+ADG有两种存储数据文件的形式，一个是将DG的数据文件放在ASM上，另一个是将数据文件放在本地磁盘上 本实验做的是数据放在本地磁盘上。 在搭建RAC+DG之前，请确保RAC是没有问题的 1.检查集群状态 grid用户下执行： crsctl status res -t RAC Primary RACDB1（主机） RACDB2（主机） 备注 Public IP 192....
ORA-19906: recovery target incarnation changed during recovery
01-04 2459
RMAN对进行数据库改名恢复的时候，报告ORA-19906: recovery target incarnation changed during recovery 问题描述 q1ebsdb.imc.com | TEST | /home/oratest > sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri J
How to install TSM Server and BA Client, Oracle TDP Client on Windows
03-12 1687
How to install TSM Server and BA Client, Oracle TDP Client on Windows... 1, TSM Server服务器端，统一管理备份节点，介质，调度等。 a，TSM Server安装介质6.2.4.000-TIV-TSMALL-WindowsX64，安装主要包括DB2和TSM服务器软件。 b，配置TSM Server实例，运行
Oracle 11G Physical Standby Database Active Data Guard - 安装配置
09-05 1636
参考官方：http://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm#i1225703 ####################### --data guard physical standby database 即Active data guard --主备库在同一台机器上，单机环境。 ###########
How to install Oracle TDP Client and BA Client on Linux
05-05 1530
tsm服务器：tsmtest，172.16.1.27 oracle服务器：ora11g
Oracle UNDO Recreate & Recovery
04-15 1521
Oracle UNDO表空间重建与恢复 UNDO表空间的数据文件从OS直接删除了，数据库还没shutduwn，UNDO需要恢复。 1，数据库正常运行状态中； 2，rm - fundotbs02.dbf， 数据库在不知情的情况下接着运行，直到有日志错误告警。 Fri Apr 19 13:27:03 2013 [3942] ORA-01554 reason: failed to g
Oracle单机RMAN迁移至RAC集群策略与配置详解
Oracle单机通过RMAN迁移至RAC集群是一个重要的技术任务，尤其对于需要快速、低停机时间的数据迁移场景。在这个过程中，涉及到的主要步骤和准备工作如下： 1. **项目背景**： - 原数据库系统老旧，无法满足业务增长...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值