使用自己的辅助实例来执行TSPITR
在执行TSPITR时Oracle建议使用RMAN来管理辅助实例的创建和推毁。然而，创建和使用自己的辅助实例也是支持的。在执行TSPITR时想要练习控制通道就会这样做。RMAN自动辅助实例使用目标数据库配置的通道来作为辅助实例的通道并在还原时使用。如果需要不同的通道设置，并且不想在目标数据库上使用configure来改变原来的通道设置，可以创建自己的辅助实例。
创建一个合适的Oracle实例来作为辅助实例要求你执行以下步骤:
1.创建辅助实例密码文件
2.创建辅助实例初始化参数文件
3.检查辅助实例网络连接
步骤1：创建辅助实例密码文件
[oracle@oracle11g dbs]$ orapwd file='/u01/app/oracle/10.2.0/db/dbs/orapwaux' password=system entries=10
步骤2：创建辅助实例参数文件
在要运行辅助实例的机器上为辅助实例创建一个客户端初始化参数文件。例如，假设参数文件存储在/u01/app/oracle/product/10.2.0/db/dbs/initaux.ora。要注意的是在参数文件中像db_file_name_convert,log_file_name_convert和control_files都是引用服务端的路径。
下面的例子显示了执行TSPITR可能要对辅助实例所设置的一些参数:
[oracle@oracle11g dbs]$ vi initaux.ora
db_name=test
db_unique_name=_test
control_files= /u01/app/oracle/oradata/auxiliary/control01.ctl
db_file_name_convert=('/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/auxiliary/')
log_file_name_convert=('/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/auxiliary/')
remote_login_passwordfile=exclusive
compatible = 10.2.0.5.0
db_block_size=8192
sga_target=160M
sga_max_size=160M
pga_aggregate_target=16M
步骤3：检查辅助实例的网络连接
辅助实例必须要有一个有效的网络服务名。在处理之前使用SQL*Plus来检查是否能连接到辅助实例。编辑tnsnames.ora文件增加网络服务名aux_test
[oracle@jingyong1 admin]$ vi tnsnames.ora
aux_test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =aux)
)
[oracle@oracle11g dbs]$ export ORACLE_SID=aux
[oracle@oracle11g dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 17 12:40:52 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272624 bytes
Variable Size 58721488 bytes
Database Buffers 104857600 bytes
Redo Buffers 2920448 bytes
SQL> show parameter service
NAME TYPE VALUE
---------------- ---------------------- ---------
service_names string _test
[oracle@oracle11g dbs]$ export ORACLE_SID=aux
[oracle@oracle11g dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 17 12:51:52 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn sys/system@aux_test as sysdba
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
数据库在nomount状态下动态监听无法识别连接者身份，会拒绝一切连接，所以报ORA-12528错误，解决此问题有两种方法，一种是去掉默认的动态监听然后创建静态监听，第二种方法是修改tnsname.ora中的内容增加(UR=A)，相对而言，第二种方法简单实用。
[oracle@jingyong1 admin]$ vi tnsnames.ora
aux_test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =_test)
(UR=A)
)
)
[oracle@jingyong1 admin]$ export ORACLE_SID=aux
[oracle@jingyong1 admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 17 10:00:50 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn sys/system@aux_test as sysdba
Connected.
SQL> show parameter service
NAME TYPE VALUE
---------------- ---------------------- ---------
service_names string _test
经过测试通过网络服务名aux_test可以连接到自己所创建的辅助实例。
下面使用自己创建的辅助实例来执行tspitr来将表空间tspitr恢复到logseq=51的时间点
步骤1：启动辅助实例为nomount状态
在开始执行RMAN TSPITR之前，使用SQL*Plus连接到辅助实例并启动到nomount状态，如果需要可以指定参数文件。
[oracle@oracle11g dbs]$ export ORACLE_SID=aux [oracle@oracle11g dbs]$ sqlplus /nolog SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 17 12:40:52 2015 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. SQL> conn / as sysdba Connected to an idle instance SQL> startup nomount pfile='/u01/app/oracle/product/10.2.0/db/dbs/initaux.ora' ORACLE instance started. Total System Global Area 113246208 bytes Fixed Size 1272288 bytes Variable Size 58721824 bytes Database Buffers 50331648 bytes Redo Buffers 2920448 bytes
因为辅助实例没有控制文件，只能将辅助实例启动到nomount状态。执行TSPITR不会创建控制文件或者尝加载或打开辅助实例。
步骤2：连接到目标实例，辅助实例和恢复目录
启动RMAN连接到目标实例，辅助实例和恢复目录:
[oracle@oracle11g admin]$ rman target sys/zzh_2046@test auxiliary sys/system@aux_test catalog rman/rman@jy Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 17 12:59:22 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: TEST (DBID=2168949517) connected to recovery catalog database connected to auxiliary database: TEST (not mounted)
步骤3：执行recover tablespace命令
现在可以执行TSPITR命令。在最简单的情况下，只需要执行recover tablespace … until命令:
再次对表tspitr执行truncate操作:
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
49 INACTIVE
50 INACTIVE
51 CURRENT
SQL> truncate table tspitr;
Table truncated.
SQL> select count(*) from tspitr;
COUNT(*)
----------
0
执行recover tablespace tspitr until logseq 51;
RMAN> run
2> {
3> allocate auxiliary channel c1 device type disk;
4> recover tablespace tspitr until logseq 51;
5> }
allocated channel: c1
channel c1: sid=35 devtype=DISK
Starting recover at 17-MAR-15
contents of Memory Script:
{
# set the until clause
set until logseq 51 thread 1;
# 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;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script
executing command: SET until clause
Starting restore at 17-MAR-15
channel c1: starting datafile backupset restore
channel c1: restoring control file
channel c1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_17/o1_mf_s_874583957_bjh9npcf_.bkp
channel c1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_17/o1_mf_s_874583957_bjh9npcf_.bkp tag=TAG20150317T115917
channel c1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/auxiliary/control01.ctl
Finished restore at 17-MAR-15
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
starting full resync of recovery catalog
full resync complete
contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until logseq 51 thread 1;
plsql < <<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'TSPITR' ||' 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
"/u01/app/oracle/oradata/auxiliary/system01.dbf";
# set a destination filename for restore
set newname for datafile 2 to
"/u01/app/oracle/oradata/auxiliary/undotbs01.dbf";
# set a destination tempfile
set newname for tempfile 1 to
"/u01/app/oracle/oradata/auxiliary/temp01.dbf";
# set a destination filename for restore
set newname for datafile 6 to
"/u01/app/oracle/oradata/test/tspitr01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 6;
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 6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "TSPITR", "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 TSPITR offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 17-MAR-15
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/auxiliary/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/auxiliary/undotbs01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf
channel c1: reading from backup piece /u02/ora_test874583877_571
channel c1: restored backup piece 1
piece handle=/u02/ora_test874583877_571 tag=TAG20150317T115756
channel c1: restore complete, elapsed time: 00:01:05
Finished restore at 17-MAR-15
datafile 6 switched to datafile copy
input datafile copy recid=21 stamp=874588453 filename=/u01/app/oracle/oradata/test/tspitr01.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 6 online
Starting recover at 17-MAR-15
starting media recovery
archive log thread 1 sequence 49 is already on disk as file /u02/1_49_870806981.dbf
archive log thread 1 sequence 50 is already on disk as file /u02/1_50_870806981.dbf
channel c1: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=48
channel c1: reading from backup piece /u02/ora_test874583954_581
channel c1: restored backup piece 1
piece handle=/u02/ora_test874583954_581 tag=TAG20150317T115914
channel c1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_48_870806981.dbf thread=1 sequence=48
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_48_870806981.dbf recid=56 stamp=874588454
archive log filename=/u02/1_49_870806981.dbf thread=1 sequence=49
archive log filename=/u02/1_50_870806981.dbf thread=1 sequence=50
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-MAR-15
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"sys/system@aux_test as sysdba\" point_in_time_recover=y tablespaces=
TSPITR file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/zzh_2046@test as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TSPITR online";
sql "alter tablespace TSPITR offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing Memory Script
Export: Release 10.2.0.5.0 - Production on Tue Mar 17 13:14:50 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TSPITR
. 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.5.0 - Production on Tue Mar 17 13:15:12 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing TSPITR's objects into TSPITR
. . importing table "TSPITR"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace TSPITR online
sql statement: alter tablespace TSPITR offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
starting full resync of recovery catalog
full resync complete
auxiliary instance file /u01/app/oracle/oradata/auxiliary/control01.ctl deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/system01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/undotbs01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/temp01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/redo01.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/redo02.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/redo03.log deleted
Finished recover at 17-MAR-15
RMAN> sql 'alter tablespace tspitr online';
sql statement: alter tablespace tspitr online
starting full resync of recovery catalog
full resync complete
SQL> select count(*) from tspitr;
COUNT(*)
----------
50641