oracle 10g auxiliary database
原文地址：oracle 10g auxiliary database 作者：neves_0
说明：
target 是指出问题的库
aux 是还原出来的备份库
注意：
target必须是归档模式
步骤
0、为target做全库的rman备份
1、为aux建立init文件
2、为aux建立相应目录及文件夹
3、指定时间点，执行恢复操作
4、得到数据，清理现场
0、为target做全库的rman备份
[oracle@centora tspitr]$ rman target sys/123456@tsm
Recovery Manager: Release 10.2.0.3.0 - Production on Wed Sep 24 12:26:58 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: GRID (DBID=1869760221)
RMAN> backup database;
Starting backup at 24-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/grid/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/grid/undotbs01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/grid/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/grid/example01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/grid/users01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/grid/test.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/grid/bearoom.dbf
channel ORA_DISK_1: starting piece 1 at 24-SEP-08
channel ORA_DISK_1: finished piece 1 at 24-SEP-08
piece
handle=/u01/app/oracle/flash_recovery_area/GRID/backupset/2008_09_24/o1_mf_nnndf_TAG20080924T122708_4fmjdxh3_.bkp
tag=TAG20080924T122708 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:06
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 24-SEP-08
channel ORA_DISK_1: finished piece 1 at 24-SEP-08
piece
handle=/u01/app/oracle/flash_recovery_area/GRID/backupset/2008_09_24/o1_mf_ncsnf_TAG20080924T122708_4fmjls6p_.bkp
tag=TAG20080924T122708 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 24-SEP-08
RMAN> quit
1、为aux建立init文件，并且修改正确后要cp到 $ORACLE_HOME/dbs目录下，
[root@centora tspitr]# cat initts.ora
*.aq_tm_processes=0
*.compatible='10.2.0.1.0'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.job_queue_processes=10
*.nls_length_semantics='BYTE'
*.open_cursors=300
*.pga_aggregate_target=254803968
*.processes=150
*.session_max_open_files=20
*.sga_target=767557632
*.undo_management='AUTO'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.audit_file_dest='/u01/app/oracle/tspitr/adump'
*.user_dump_dest='/u01/app/oracle/tspitr/udump'
*.background_dump_dest='/u01/app/oracle/tspitr/bdump'
*.core_dump_dest='/u01/app/oracle/tspitr/cdump'
*.control_files='/u01/app/oracle/tspitr/control01.ctl'
*.db_name=tspitr
*.db_file_name_convert=('/u01/app/oracle/oradata/grid','/u01/app/oracle/tspitr')
*.log_file_name_convert=('/u01/app/oracle/flash_recovery_area','/u01/app/oracle/tspitr/flash_recovery_area')
[oracle@centora tspitr]$ cp initts.ora /u01/app/oracle/product/10.2.0/db_1/dbs/inittspitr.ora
2、为aux建立相应目录及文件夹
3、指定时间点，执行恢复操作
[oracle@centora tspitr]$ export ORACLE_SID=tspitr
[oracle@centora tspitr]$ echo $ORACLE_SID
tspitr
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 767557632 bytes
Fixed Size 1264136 bytes
Variable Size 205522424 bytes
Database Buffers 557842432 bytes
Redo Buffers 2928640 bytes
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@centora tspitr]$ rman target sys/123456@tsm auxiliary /
Recovery Manager: Release 10.2.0.3.0 - Production on Wed Sep 24 14:51:21 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: GRID (DBID=1869760221)
connected to auxiliary database: TSPITR (not mounted)
RMAN>
RMAN> run {
2> SET until time "TO_DATE('2008-09-24 12:30:40','YYYY-MM-DD HH24:MI:SS')";
3> DUPLICATE TARGET DATABASE TO TSPITR
4> LOGFILE
5> GROUP 1 ('/u01/app/oracle/tspitr/redo01.log') SIZE 10M ,
6> GROUP 2 ('/u01/app/oracle/tspitr/redo02.log') SIZE 10M ,
7> GROUP 3 ('/u01/app/oracle/tspitr/redo03.log') SIZE 10M ;
8> }
executing command: SET until clause
using target database control file instead of recovery catalog
Starting Duplicate Db at 24-SEP-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
contents of Memory Script:
{
set until scn 877467089;
set newname for datafile 1 to
"/u01/app/oracle/tspitr/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/tspitr/undotbs01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/tspitr/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/tspitr/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/tspitr/example01.dbf";
set newname for datafile 6 to
"/u01/app/oracle/tspitr/test.dbf";
set newname for datafile 7 to
"/u01/app/oracle/tspitr/bearoom.dbf";
restore
check readonly
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
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-SEP-08
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/tspitr/system01.dbf
restoring datafile 00002 to /u01/app/oracle/tspitr/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/tspitr/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/tspitr/users01.dbf
restoring datafile 00005 to /u01/app/oracle/tspitr/example01.dbf
restoring datafile 00006 to /u01/app/oracle/tspitr/test.dbf
restoring datafile 00007 to /u01/app/oracle/tspitr/bearoom.dbf
channel
ORA_AUX_DISK_1: reading from backup piece
/u01/app/oracle/flash_recovery_area/GRID/backupset/2008_09_24/o1_mf_nnndf_TAG20080924T122708_4fmjdxh3_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece
handle=/u01/app/oracle/flash_recovery_area/GRID/backupset/2008_09_24/o1_mf_nnndf_TAG20080924T122708_4fmjdxh3_.bkp
tag=TAG20080924T122708
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 24-SEP-08
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TSPITR" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/tspitr/redo01.log' ) SIZE 10 M ,
GROUP 2 ( '/u01/app/oracle/tspitr/redo02.log' ) SIZE 10 M ,
GROUP 3 ( '/u01/app/oracle/tspitr/redo03.log' ) SIZE 10 M
DATAFILE
'/u01/app/oracle/tspitr/system01.dbf'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=666283965 filename=/u01/app/oracle/tspitr/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=666283965 filename=/u01/app/oracle/tspitr/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=666283965 filename=/u01/app/oracle/tspitr/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=666283965 filename=/u01/app/oracle/tspitr/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=666283965 filename=/u01/app/oracle/tspitr/test.dbf
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=666283965 filename=/u01/app/oracle/tspitr/bearoom.dbf
contents of Memory Script:
{
set until time "TO_DATE('2008-09-24 12:30:40','YYYY-MM-DD HH24:MI:SS')";
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 24-SEP-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
starting media recovery
archive
log thread 1 sequence 67 is already on disk as file
/u01/app/oracle/flash_recovery_area/GRID/archivelog/2008_09_24/o1_mf_1_67_4fmld6xo_.arc
archive
log
filename=/u01/app/oracle/flash_recovery_area/GRID/archivelog/2008_09_24/o1_mf_1_67_4fmld6xo_.arc
thread=1 sequence=67
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-SEP-08
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 767557632 bytes
Fixed Size 1264136 bytes
Variable Size 205522424 bytes
Database Buffers 557842432 bytes
Redo Buffers 2928640 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TSPITR" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/tspitr/redo01.log' ) SIZE 10 M ,
GROUP 2 ( '/u01/app/oracle/tspitr/redo02.log' ) SIZE 10 M ,
GROUP 3 ( '/u01/app/oracle/tspitr/redo03.log' ) SIZE 10 M
DATAFILE
'/u01/app/oracle/tspitr/system01.dbf'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/tspitr/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/tspitr/undotbs01.dbf";
catalog clone datafilecopy "/u01/app/oracle/tspitr/sysaux01.dbf";
catalog clone datafilecopy "/u01/app/oracle/tspitr/users01.dbf";
catalog clone datafilecopy "/u01/app/oracle/tspitr/example01.dbf";
catalog clone datafilecopy "/u01/app/oracle/tspitr/test.dbf";
catalog clone datafilecopy "/u01/app/oracle/tspitr/bearoom.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /u01/app/oracle/tspitr/temp01.dbf in control file
cataloged datafile copy
datafile copy filename=/u01/app/oracle/tspitr/undotbs01.dbf recid=1 stamp=666283978
cataloged datafile copy
datafile copy filename=/u01/app/oracle/tspitr/sysaux01.dbf recid=2 stamp=666283978
cataloged datafile copy
datafile copy filename=/u01/app/oracle/tspitr/users01.dbf recid=3 stamp=666283978
cataloged datafile copy
datafile copy filename=/u01/app/oracle/tspitr/example01.dbf recid=4 stamp=666283979
cataloged datafile copy
datafile copy filename=/u01/app/oracle/tspitr/test.dbf recid=5 stamp=666283979
cataloged datafile copy
datafile copy filename=/u01/app/oracle/tspitr/bearoom.dbf recid=6 stamp=666283979
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=666283978 filename=/u01/app/oracle/tspitr/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=666283978 filename=/u01/app/oracle/tspitr/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=666283978 filename=/u01/app/oracle/tspitr/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=666283979 filename=/u01/app/oracle/tspitr/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=666283979 filename=/u01/app/oracle/tspitr/test.dbf
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=666283979 filename=/u01/app/oracle/tspitr/bearoom.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 24-SEP-08
RMAN>
RMAN> quit
Recovery Manager complete.
[oracle@centora tspitr]$ sqlplus bear/123456
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Sep 24 14:53:34 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
tspitr
SQL>
4、得到数据，清理现场
5、问题及处理
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/24/2008 12:09:02
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "test"
--重新与target同步一下
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/24/2008 14:39:08
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-01504: database name 'TSPITR' does not match parameter db_name 'GRID'
--参数文件没有指对
http://www.itpub.net/viewthread.php?tid=479068&highlight=TSPITR
如果不想复制一些表空间，可以skip它们，脚本类似
run {
SET until time "TO_DATE('2006-01-09 17:26:00','YYYY-MM-DD HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO AUX
SKIP TABLESPACE indx,tools;
}
但是system，undo表空间是不能skip的。
还可以指定redolog文件的大小，脚本类似
run {
SET until time "TO_DATE('2006-01-09 17:26:00','YYYY-MM-DD HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO AUX
SKIP TABLESPACE indx,tools
LOGFILE
GROUP 1 ('/opt/oracle/admin/aux/redo01.log') SIZE 10M ,
GROUP 2 ('/opt/oracle/admin/aux/redo02.log') SIZE 10M ,
GROUP 3 ('/opt/oracle/admin/aux/redo03.log') SIZE 10M ;
}
--以下为target操作
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 63
Current log sequence 65
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 767557632 bytes
Fixed Size 1264136 bytes
Variable Size 301991416 bytes
Database Buffers 461373440 bytes
Redo Buffers 2928640 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 63
Next log sequence to archive 65
Current log sequence 65
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select sysdate from dual;
SYSDATE
---------
24-SEP-08
SQL> ho date
Wed Sep 24 12:00:07 CST 2008
SQL> insert into test values(100);
insert into test values(100)
