一、手工创建辅助实例
1。创建密码文件
orapwd file=F:oracleproduct10.1.0em_1databasePWDaux1.ora password=liang
2. 创建参数文件initaux1.ora
DB_NAME=test(注:与源数据库的db_name相同)
DB_UNIQUE_NAME=aux1_test(注:需要唯一名字)
sga_target=250000000(需要足够大,否则出现ora-4031错误。当sga_target为默认值时,依然出现ORA-04031: unable to allocate 100 bytes of shared memory ("shared
pool","declare fullname varchar2 ( ...","PL/SQL MPCODE","Machine Code Part Holder"))
CONTROL_FILES="F:oracleproduct10.1.0oradataaux1control01.ctl"
REMOTE_LOGIN_PASSWORDFILE=exclusive
COMPATIBLE =10.2.0.1.0
DB_BLOCK_SIZE=8192
指定辅助集文件的位置:
db_file_name_convert=("F:oracleproduct10.1.0oradatatest", "F:oracleproduct10.1.0oradataaux1")
log_file_name_convert=("F:oracleproduct10.1.0oradatatest", "F:oracleproduct10.1.0oradataaux1")
3。创建oracle实例aux1
oradim -new -sid aux1
以上是创建辅助实例的过程,创建好后可以用rman 连接(必须事先做好rman全备份)
二。
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:Documents and Settingsliang>set oracle_sid=aux1(用操作系统认证,需设置该环境变量)
C:Documents and Settingsliang>rman target sys/liang@test auxiliary /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jan 30 19:13:16 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (DBID=1911386690)
connected to auxiliary database: TEST (not mounted)
三。进行表空间点时间点恢复。
RMAN> RUN {
2> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
3> ALLOCATE auxiliary CHANNEL c2 DEVICE TYPE disk;
4> RECOVER TABLESPACE users UNTIL TIME '2007-01-30 19:00:00';
5> release channel c1;
6> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=158 devtype=DISK
allocated channel: c2
channel c2: sid=36 devtype=DISK
Starting recover at 2007-01-30 19:14:09
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time
List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1
contents of Memory Script:
{
# set the until clause
set until time "2007-01-30 19:00:00";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 2007-01-30 19:14:10
channel c2: starting datafile backupset restore
channel c2: restoring control file
channel c2: reading from backup piece F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTBACKUPSET2007_01_30O1_MF_
NCSNF_TAG20070130T170959_2VY2WQ5V_.BKP
channel c2: restored backup piece 1
piece handle=F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTBACKUPSET2007_01_30O1_MF_NCSNF_TAG20070130T170959_
2VY2WQ5V_.BKP tag=TAG20070130T170959
channel c2: restore complete, elapsed time: 00:00:01
output filename=F:ORACLEPRODUCT10.1.0ORADATAAUX1CONTROL01.CTL
Finished restore at 2007-01-30 19:14:11
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "2007-01-30 19:00:00";
plsql <<declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'USERS' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set a destination filename for restore
set newname for datafile 1 to
"F:ORACLEPRODUCT10.1.0ORADATAAUX1SYSTEM01.DBF";
# set a destination filename for restore
set newname for datafile 2 to
"F:ORACLEPRODUCT10.1.0ORADATAAUX1UNDOTBS01.DBF";
# set a destination tempfile
set newname for tempfile 1 to
"F:ORACLEPRODUCT10.1.0ORADATAAUX1TEMP01.DBF";
# set a destination filename for restore
set newname for datafile 4 to
"F:ORACLEPRODUCT10.1.0ORADATATESTUSERS01.DBF";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 4;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 4 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace USERS offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2007-01-30 19:14:19
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00001 to F:ORACLEPRODUCT10.1.0ORADATAAUX1SYSTEM01.DBF
restoring datafile 00002 to F:ORACLEPRODUCT10.1.0ORADATAAUX1UNDOTBS01.DBF
restoring datafile 00004 to F:ORACLEPRODUCT10.1.0ORADATATESTUSERS01.DBF
channel c2: reading from backup piece F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTBACKUPSET2007_01_30O1_MF_
NNNDF_TAG20070130T170959_2VY2V889_.BKP
channel c2: restored backup piece 1
piece handle=F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTBACKUPSET2007_01_30O1_MF_NNNDF_TAG20070130T170959_
2VY2V889_.BKP tag=TAG20070130T170959
channel c2: restore complete, elapsed time: 00:00:47
Finished restore at 2007-01-30 19:15:07
datafile 4 switched to datafile copy
input datafile copy recid=4 stamp=613250107 filename=F:ORACLEPRODUCT10.1.0ORADATATESTUSERS01.DBF
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 4 online
Starting recover at 2007-01-30 19:15:08
starting media recovery
archive log thread 1 sequence 24 is already on disk as file F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTARCHI
VELOG2007_01_30O1_MF_1_24_2VY31OJ3_.ARC
archive log thread 1 sequence 25 is already on disk as file F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTARCHI
VELOG2007_01_30O1_MF_1_25_2VY31RXS_.ARC
archive log thread 1 sequence 26 is already on disk as file F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTARCHI
VELOG2007_01_30O1_MF_1_26_2VY31Y1Y_.ARC
archive log thread 1 sequence 27 is already on disk as file F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTARCHI
VELOG2007_01_30O1_MF_1_27_2VYB48KL_.ARC
archive log filename=F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTARCHIVELOG2007_01_30O1_MF_1_24_2VY31OJ3_.A
RC thread=1 sequence=24
archive log filename=F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTARCHIVELOG2007_01_30O1_MF_1_25_2VY31RXS_.A
RC thread=1 sequence=25
archive log filename=F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTARCHIVELOG2007_01_30O1_MF_1_26_2VY31Y1Y_.A
RC thread=1 sequence=26
archive log filename=F:ORACLEPRODUCT10.1.0FLASH_RECOVERY_AREATESTARCHIVELOG2007_01_30O1_MF_1_27_2VYB48KL_.A
RC thread=1 sequence=27
media recovery complete, elapsed time: 00:00:05
Finished recover at 2007-01-30 19:15:16
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid ="/@ as sysdba" point_in_time_recover=y tablespaces=
USERS file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid ="sys/liang@test as sysdba" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace USERS online";
sql "alter tablespace USERS offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
Export: Release 10.2.0.1.0 - Production on Tue Jan 30 19:15:25 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining Scoring Engine options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table T
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete
database closed
database dismounted
Oracle instance shut down
Import: Release 10.2.0.1.0 - Production on Tue Jan 30 19:15:44 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining Scoring Engine options
Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SYS's objects into SYS
. importing LCM's objects into LCM
. . importing table "T"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace USERS online
sql statement: alter tablespace USERS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
auxiliary instance file F:ORACLEPRODUCT10.1.0ORADATAAUX1CONTROL01.CTL deleted
auxiliary instance file F:ORACLEPRODUCT10.1.0ORADATAAUX1SYSTEM01.DBF deleted
auxiliary instance file F:ORACLEPRODUCT10.1.0ORADATAAUX1UNDOTBS01.DBF deleted
auxiliary instance file F:ORACLEPRODUCT10.1.0ORADATAAUX1TEMP01.DBF deleted
auxiliary instance file F:ORACLEPRODUCT10.1.0ORADATAAUX1REDO01.LOG deleted
auxiliary instance file F:ORACLEPRODUCT10.1.0ORADATAAUX1REDO02.LOG deleted
auxiliary instance file F:ORACLEPRODUCT10.1.0ORADATAAUX1REDO03.LOG deleted
Finished recover at 2007-01-30 19:15:50
released channel: c1
RMAN>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9599/viewspace-472943/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9599/viewspace-472943/