Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1011172
  • 博文数量: 584
  • 博客积分: 2293
  • 博客等级: 大尉
  • 技术积分: 3045
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-28 11:15
文章分类

全部博文(584)

文章存档

2012年(532)

2011年(47)

2009年(5)

我的朋友

分类:

2012-05-28 10:38:24

原文地址: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)

阅读(657) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~