oracle 还原点

正常还原点
创建正常还原点是给一个SCN或指定时间点指定一个还原点名称。因此,还原点实际上可以看作是SCN的标记或别名。在执行任何操作之前你可能想要撤消该操作,你可以创建一个正常还原点。控制文件会存储还原点名称与SCN,创建还原点可以消除提前记录SCN或在使用闪回时需要判断正确的SCN操作。

如果使用闪回或按时间点恢复,那么你可以使用还原点名称来代夫时间表达式或SCN。下面的命令支持使用还原点:
1.RMAN中的recover database与flashback database命令
2.SQL中的flashback table语句

正常的还原点是轻量级的。控制文件可以维护上千个正常还原点而不会影响数据库性能。正常还原点如果不手动删除,当执行恢复不再需要它们时会从控制文件中将其删除。
下面举例说明:
1.创建正常还原点

SQL> create restore point insert_point;

Restore point created.


SQL> set long 200
SQL> set linesize 200
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
  2  GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
  3  FROM V$RESTORE_POINT;

NAME                        SCN TIME                                                                        DATABASE_INCARNATION# GUA STORAGE_SIZE
-------------------- ---------- --------------------------------------------------------------------------- --------------------- --- ------------
INSERT_POINT            1187948 23-APR-15 05.18.21.000000000 PM                                                                 3 NO             0

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select current_scn,to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;

CURRENT_SCN TO_CHAR(SCN_TO_TIME
----------- -------------------
    1187983 2015-04-23 17:18:59

2.新创建一个表test:

SQL> create table test as select * from dba_objects;

Table created.

SQL> select current_scn,to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;

CURRENT_SCN TO_CHAR(SCN_TO_TIME
----------- -------------------
    1188096 2015-04-23 17:20:20

3.将数据库闪回到创建正常还原点的时间点

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  188313600 bytes
Fixed Size                  1363496 bytes
Variable Size             150995416 bytes
Database Buffers           33554432 bytes
Redo Buffers                2400256 bytes
Database mounted.
SQL> flashback database to scn 1187983;

Flashback complete.

SQL> alter database open read only;

Database altered.

4.检查表test是否存在,因为表test是在创建还原点之后所创建,当将数据库闪回到还原点后表test也就不存了:

SQL> select count(*) from test;
select count(*) from test
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

受保护还原点
像正常还原点一样,受保护的还原点也是恢复操作中所指定SCN的一个别名。唯一的区别是受保护的还原点除了显式删除之外是不会从控制文件中删除的。通常来说,使用正常还原点执行的命令也可以使用受保护还原点来作为SCN的别名,除了另有说明之外,使用正常还原点与受保护还原点的使用方法是一样的

即使生成闪回日志被禁用,受保护还原点确保你能使用闪回数据库将数据文件恢复到还原点SCN所对应的状态。如果启用闪回日志,那么受保护的还原点会强制保留将数据库闪回到在最早创建的受保护还原点后的任意SCN所需要的闪回日志。因此,如果启用了闪回日志,可以将数据库闪回到受保护还原点后的任意SCN而不只是单个SCN。如果禁用了闪回日志,那么在执行flashback database命令时不能直接指定受保护还原点与当前时间之间的SCN。然后可以先闪回到受保护还原点再恢复到受保护还原点与当前时间之前的SCN。

闪回数据库的命令是flashback database to PIT,PIT可以是SCN、时间或还原点,顾名思义,就是将整个数据库回退到指定的一个时间点,实际上是数据库不完全恢复的另一种方式。真正的不完全恢复需要消耗的时间与数据库的大小有密切联系,数据库越庞大,需要的时间就越多。

闪回数据库技术改变了这个窘境,其处理问题方式可以着重于如何将相对较短的时间内发生的变更去除,而不是如何还原足够旧的备份(很花时间了),再利用归档日志前滚至指定时间。.

闪回数据库需要使用两种日志:闪回日志和重做日志。其中重做日志已为大家所知,而闪回日志就像是重做日志的反作用力,闪回日志的记载正好与重做日志的记载相反。比如,逻辑上重做日志记录insert命令的重做记录,闪回日志就记录delete命令的重做记录(当然实际上没那么简单)。在发起闪回操作时,只要根据写入闪回日志的相反顺序,即后写先读的顺序,将闪回记录从闪回日志读出并执行其记录的变更就能够将数据库在时间轴上倒推。

闪回日志的保存路径一定是快速恢复区的子目录,保存的期限则由参数db_flashback_ retention_target控制(单位为分钟),凡是超出保存期限的闪回日志将会在快速恢复区空间紧张的时候被自动删除。比如,指定数据库保留两天的闪回日志和归档日志:

SQL> alter system set db_flashback_retention_target=2880;

System altered.

使用v$database.FLASHBACK_ON可以查看闪回日志是否已启用,现在是“NO”,表示尚未启用:

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

在确保启用了归档模式之后,使用以下命令可以启用闪回日志,即闪回数据库功能:

SQL> alter database flashback on;

Database altered.

从此,在快速恢复区的flashback子目录下将会出现扩展名为.flb的文件,它们就是闪回日志。

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

闪回数据库命令的语法很简单:在进入MOUNT状态后执行flashback database to scn …或flashback database to timestamp …。

受保护还原点与存储快照
在实践中,受保护还原点可以替代存储快照。存储快照通常被用来在执行危险操作之前来保护数据库,比如大范围的数据库更新或应用打补丁或升级。相对于创建快照或复制一个数据库进行测试操作来说,你可以在主库或物理备库中创建一个受保护还原点。

闪回数据库与受保护还原点日志
闪回数据库日志与受保护还原点调用改变之前的数据文件块镜像,flashback database命令可以使用这些镜像来将数据文件还原到之前的状态。正常闪回日志与受保护还原点日志最主要的差别是当闪回区出现空间压力时是否这些日志会被删除。这种差异会影响日志的空间使用和数据库性能。你的恢复目录部分决定了是否启用闪回日志或者还原点,或都两者都使用。当单独或一起使用这些功能时对空间使用和性能的影响是需要考虑的。

受保护还原点与闪回区空间之关的系
当创建,保留,覆盖与删除闪回区中的闪回日志时存在以下规则:
1.如果闪回区有足够空间,那么当需要满足闪回保留目标时生成闪回日志。
2.如果闪回日志过旧不再满足闪回保留目标,那么可以重用这些闪回日志。
3.如果数据库必须创建闪回日志并且闪回区已经被使用完或者没有磁盘空间,那么旧的闪回日志会被重用。重用旧的闪回日志会缩短闪回数据库窗口。如果因为空间压力问题有过多的闪回日志被重用,那么闪回保留目标可能不会被满足。
4.如果闪回区填满,那么概括闪回区规则当闪回区要为其它文件提供空间时会自动删除归档重做日志来回收空间。在这种情况下,flashback database命令需要使用的重做日志的任何闪回日志也会被删除。概括闪回区规则,当出现以下情况下文件使用的空间会被回收。
a.文件被报告为过期并且闪回数据库不需要该文件。例如该文件保留的时间超过了db_flashback_retention_target参数设置
b.文件已经备份磁带
5.如果为了满足受保护还原点闪回区中没有文件满足删除条件。因为,闪回日志和其它为了满足受保护还原点的其它文件,额外的为了满足备份保留策略所需要的文件都可能会造成闪回区被填满。

当创建受保护还原点可以考虑不完全启动闪回日志,但需要监控闪回区的可用空间。如果为了满足你的保留策略和受保护还原点,闪回区中没有满足删除条件的文件,如果遇到磁盘空间被填满的情况时,数据库会执行删除。

禁用闪回日志与受保护还原点
假设在数据库禁用闪回日志的情况下创建了受保护还原点。在这种情况下,在受保护还原点创建时第一次被修改的数据文件块,数据库会将被修改前的数据块镜像存储在闪回日志中。因此闪回日志保留了每一个被修改的数据块在创建受保护还原点时的内容。后面对相同数据块的修改将不会被捕获除非在数据块最后被之前之前又创建了一个受保护还原点。

这种记录日志方法有以下重要的结果:
1.flashback database可以通过使用数据块镜像重新构造在创建受保护还原点时数据文件中的内容。
2.对于反复修改相同的数据,相比对正常的闪回日志受保护还原点所要使用的空间更少,因为每一个被修改的数据块只会被记录一次。应用程序有较少插入操作可能从节省磁盘空间中受益。当应用程序有大量插入或批量插入操作时这种优势就没有了。受保护还原点的日志开锁在禁用闪回日志时是很小的。

假如你的主要目标是要能将数据库恢复到受保护还原点所创建的时间点,那么在这种情况下,禁用闪回日志而只使用受保护还原点更有效。例如假设要对数据库执行升级,可以在启动升级之前创建一个受保护还原点,如果升级失败,那么可以执行flashback database来恢复数据库。

启用闪回日志与受保护还原点
如果启用闪回日志并定义一个或多个受保护还原点,那么数据库会执行正常的闪回日志操作。在这种情况下,闪回区将会保留将数据库恢复到受保护还原点与当前时间之间任何时间点所需要的所有闪回日志。闪回日志如果满足受保护还原点的要求在出现空间压力时是不会被删除的。启用闪回日志会占用一些性能开销。依赖于数据库的工作负载,可能会对闪回区造成极大的空间压力。因此应该要监控闪回区的空间使用情况。

使用闪回数据库与受保护还原点的条件
为了能成功执行闪回数据库和受保护还原点操作,必须首先设置一些关键的数据库选项。在启用闪回数据库之前进行以下数据库设置:
1.你的数据库必须运行在archivelog模式下,因为闪回数据库需要使用归档重做日志。
2.必须设置了闪回区,因为闪回日志只能存储在闪回区中。
3.对于RAC数据库,闪回区必须是集群文件系统或ASM

为了使用受保护还原点,数据库必须满足compatible参数必须设置为10.2.0或更高版本。但在使用正常还原点之前没有任何特定要求。

下面是举例来说明:
在启用闪回日志的情况下,创建受保护还原点
1.创建受保护还原点

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> select current_scn,to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;

CURRENT_SCN TO_CHAR(SCN_TO_TIME
----------- -------------------
    1187919 2015-04-23 17:17:26

SQL> create restore point  create_point guarantee flashback database;

Restore point created.

SQL> set long 200
SQL> set linesize 200
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
  2  GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
  3  FROM V$RESTORE_POINT;

NAME                        SCN TIME                                                                        DATABASE_INCARNATION# GUA STORAGE_SIZE
-------------------- ---------- --------------------------------------------------------------------------- --------------------- --- ------------
CREATE_POINT            1187948 23-APR-15 05.18.21.000000000 PM                                                                 3 YES     52428800

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select current_scn,to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;

CURRENT_SCN TO_CHAR(SCN_TO_TIME
----------- -------------------
    1187983 2015-04-23 17:18:59

2.新建表test

SQL> create table test as select * from dba_objects;

Table created.

SQL> select current_scn,to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;

CURRENT_SCN TO_CHAR(SCN_TO_TIME
----------- -------------------
    1188096 2015-04-23 17:20:20

因为我们启用了闪回日志,所以可以将数据库闪回到受保护还原点(create_point)SCN:1187948与当前时间2015-04-23 17:20:20 SCN:1188096之间的任意SCN所对应的状态。那么我们将数据库闪回到SCN为1187983,这时表test是还没有创建的。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  188313600 bytes
Fixed Size                  1363496 bytes
Variable Size             150995416 bytes
Database Buffers           33554432 bytes
Redo Buffers                2400256 bytes
Database mounted.
SQL> flashback database to scn 1187983;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from test;
select count(*) from test
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

在确认之前不要以resetlogs选项open数据库,这样可以多次执行闪回数据库操作,也可以撤消闪回数据库操作,下面是撤消闪回数据库操作是通过执行完全恢复来实现的:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  188313600 bytes
Fixed Size                  1363496 bytes
Variable Size             150995416 bytes
Database Buffers           33554432 bytes
Redo Buffers                2400256 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select count(*) from test;

  COUNT(*)
----------
     86741

下面我们在禁用闪回日志的情况下执行闪回数据库操作:
1.禁用闪回日志并创建受保护还原点

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database flashback off;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  188313600 bytes
Fixed Size                  1363496 bytes
Variable Size             150995416 bytes
Database Buffers           33554432 bytes
Redo Buffers                2400256 bytes
Database mounted.

SQL> create restore point delete_point guarantee flashback database;


Restore point created.

SQL> alter database open;

Database altered.


SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY



SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
  2  GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
  3  FROM V$RESTORE_POINT;

NAME                        SCN TIME                                                                        DATABASE_INCARNATION# GUA STORAGE_SIZE
-------------------- ---------- --------------------------------------------------------------------------- --------------------- --- ------------
DELETE_POINT            1192464 23-APR-15 07.36.16.000000000 PM                                                                 3 YES     52428800

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select current_scn,to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;

CURRENT_SCN TO_CHAR(SCN_TO_TIME
----------- -------------------
    1192860 2015-04-23 19:37:51

2.删除表test

SQL> delete from test;

86743 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
         0

SQL> select current_scn,to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;

CURRENT_SCN TO_CHAR(SCN_TO_TIME
----------- -------------------
    1193268 2015-04-23 19:38:57

现在我们来测试看在禁用闪回日志的情况下能不能将数据库闪回到受保护还原点(delete_point)scn:1192464与当前时间SCN:1193268之间的任意SCN,这里我们闪回到SCN:1192860,这里表test的记录还没有被删除

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  188313600 bytes
Fixed Size                  1363496 bytes
Variable Size             150995416 bytes
Database Buffers           33554432 bytes
Redo Buffers                2400256 bytes
Database mounted.
SQL> flashback database to scn 1192860;
flashback database to scn 1192860
*
ERROR at line 1:
ORA-38726: Flashback database logging is not on.

错误提示已经说明了没有闪回日志,所以不能将数据库闪回到受保护还原点与当前时间之间的任意SCN。

下面将表test闪回到受保护还原点SCN:1192464

SQL> flashback database to restore point delete_point;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from test;

  COUNT(*)
----------
     86743

从执行结果可知,已经恢复到受保护还原点所对应的状态,表test的数据恢复回来了。

从上面的执行结果可以看到在没有启用闪回日志的情况可以将数据库闪回到受保护还原点。如果要想在没有启用闪回日志的情况下将数据库闪回到受保护还原点与当前时间点之间的SCN:1192860,可以先将数据库闪加到受保护还原点,再执行恢复将数据库恢复到SCN:1192860。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  188313600 bytes
Fixed Size                  1363496 bytes
Variable Size             150995416 bytes
Database Buffers           33554432 bytes
Redo Buffers                2400256 bytes
Database mounted.
SQL> flashback database to restore point delete_point;

Flashback complete.

SQL> alter  session  set  nls_date_format  =  'yyyy-mm-dd  hh24:mi:ss';

Session altered.

因为SCN:1192860,对应的时间就是2015-04-23 19:37:51,这里如果直接用SCN:1192860执行恢复会报以下错误 :

SQL> recover database until scn 1192860;
ORA-00277: illegal option to the UNTIL recovery flag SCN



SQL> recover database until time '2015-04-23 19:37:51';
Media recovery complete.
SQL> alter database open read only;

Database altered.

SQL> select count(*) from test;

  COUNT(*)
----------
     86743

从执行结果可知,已经恢复到受保护还原点所对应的状态,表test的数据恢复回来了。

weblogic启动不能锁定AdminServer.lok的故障处理

weblogic的版本是10.3,操作系统是linux,安装weblogic创建了一个weblogic用户,由于使用root用户修改了一些性能参数后,使用了root用户启动了一次weblogic,而原来启动weblogic的脚本是使用了weblogic用户,在使用weblogic用户启动weblogic时出现了不能锁定AdminServer.lok文件的错误,具体错误信息如下所示:

[weblogic@localhost ~]$  cd /home/weblogic/Oracle/Middleware/user_projects/domains/sbkwsjb/bin
[weblogic@localhost bin]$ ./startWebLogic.sh
.
.
JAVA Memory arguments: -Xms1024m -Xmx2048m -XX:CompileThreshold=8000 -XX:PermSize=256m  -XX:MaxPermSize=512m
.
WLS Start Mode=Development
.
CLASSPATH=/home/weblogic/Oracle/Middleware/patch_wls1036/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/home/weblogic/Oracle/Middleware/patch_ocp371/profiles/default/sys_manifes
t_classpath/weblogic_patch.jar:/usr/java64/jdk1.6.0_45/lib/tools.jar:/home/weblogic/Oracle/Middleware/wlserver_10.3/server/lib/weblogic_sp.jar:/home/weblogic/Oracle/Middleware/wlserver_10.3/
server/lib/weblogic.jar:/home/weblogic/Oracle/Middleware/modules/features/weblogic.server.modules_10.3.6.0.jar:/home/weblogic/Oracle/Middleware/wlserver_10.3/server/lib/webservices.jar:/home
/weblogic/Oracle/Middleware/modules/org.apache.ant_1.7.1/lib/ant-all.jar:/home/weblogic/Oracle/Middleware/modules/net.sf.antcontrib_1.1.0.0_1-0b2/lib/ant-contrib.jar:/home/weblogic/Oracle/Mi
ddleware/wlserver_10.3/common/derby/lib/derbyclient.jar:/home/weblogic/Oracle/Middleware/wlserver_10.3/server/lib/xqrl.jar:.:/usr/java64/jdk1.6.0_45/lib:/usr/java64/jdk1.6.0_45/lib/dt.jar:/u
sr/java64/jdk1.6.0_45/lib/tools.jar
.
PATH=/home/weblogic/Oracle/Middleware/wlserver_10.3/server/bin:/home/weblogic/Oracle/Middleware/modules/org.apache.ant_1.7.1/bin:/usr/java64/jdk1.6.0_45/jre/bin:/usr/java64/jdk1.6.0_45/bin:/
usr/lib64/qt-3.3/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/java64/jdk1.6.0_45/bin:/home/weblogic/bin
.
***************************************************
*  To start WebLogic Server, use a username and   *
*  password assigned to an admin-level user.  For *
*  server administration, use the WebLogic Server *
*  console at http://hostname:port/console        *
***************************************************
starting weblogic with Java version:
java version "1.6.0_45"
Java(TM) SE Runtime Environment (build 1.6.0_45-b06)
Java HotSpot(TM) 64-Bit Server VM (build 20.45-b01, mixed mode)
Starting WLS with line:
/usr/java64/jdk1.6.0_45/bin/java -client   -Xms1024m -Xmx2048m -XX:CompileThreshold=8000 -XX:PermSize=256m  -XX:MaxPermSize=512m -Dweblogic.Name=AdminServer -Djava.security.policy=/home/webl
ogic/Oracle/Middleware/wlserver_10.3/server/lib/weblogic.policy  -Xverify:none  -da -Dplatform.home=/home/weblogic/Oracle/Middleware/wlserver_10.3 -Dwls.home=/home/weblogic/Oracle/Middleware
/wlserver_10.3/server -Dweblogic.home=/home/weblogic/Oracle/Middleware/wlserver_10.3/server   -Dweblogic.management.discover=true  -Dwlw.iterativeDev= -Dwlw.testConsole= -Dwlw.logErrorsToCon
sole= -Dweblogic.ext.dirs=/home/weblogic/Oracle/Middleware/patch_wls1036/profiles/default/sysext_manifest_classpath:/home/weblogic/Oracle/Middleware/patch_ocp371/profiles/default/sysext_mani
fest_classpath  weblogic.Server
bea -000362 server failed. Reason:

There are 1 nested errors:

weblogic.management.ManagementException: Unable to obtain lock on /home/weblogic/Oracle/Middleware/user_projects/domains/sbkwsjb/servers/AdminServer/tmp/AdminServer.lok. Server may already b
e running
        at weblogic.management.internal.ServerLocks.getServerLock(ServerLocks.java:206)
        at weblogic.management.internal.ServerLocks.getServerLock(ServerLocks.java:67)
        at weblogic.management.internal.DomainDirectoryService.start(DomainDirectoryService.java:74)
        at weblogic.t3.srvr.ServerServicesManager.startService(ServerServicesManager.java:461)
        at weblogic.t3.srvr.ServerServicesManager.startInStandbyState(ServerServicesManager.java:166)
        at weblogic.t3.srvr.T3Srvr.initializeStandby(T3Srvr.java:881)
        at weblogic.t3.srvr.T3Srvr.startup(T3Srvr.java:568)
        at weblogic.t3.srvr.T3Srvr.run(T3Srvr.java:469)
        at weblogic.Server.main(Server.java:71)


出现这种情况的原因有两种可能但不限于,一是执行kill -9杀死weblogic进程,一种是使用的不是安装weblogic的用户比如root用户来启动weblogic。这里出现这种情况之前,既使用了kill -9杀死weblogic进程,也使用root用户(而不是weblogic用户)来启动weblogic。

对于这种情况解决方法有以下三种
1.是修改AdminServer.lok文件的权限,让weblogic用户能锁定它.
2.删除AdminServer.lok文件并重新使用weblogic用户来启动weblogic服务.
3.杀死仍然运行的服务进程并重新weblogic服务.

这里我采用的是第一种解决方法:

[root@localhost bin]# cd /home/weblogic/Oracle/Middleware/user_projects/domains/sbkwsjb/servers/AdminServer/tmp/
[root@localhost tmp]# ls -lrt
total 12
drwxr----- 5 weblogic bea  4096 Dec 29 19:09 _WL_internal
drwxr----- 3 weblogic bea  4096 Mar 17 17:53 _WL_user
-rw-r----- 1 weblogic bea   264 Apr 21 17:11 WebServiceUtils.ser
-rw-r----- 1 root     root    0 Apr 21 17:24 AdminServer.lok
[root@localhost tmp]# chown weblogic:bea AdminServer.lok
[root@localhost tmp]# ls -lrt
total 12
drwxr----- 5 weblogic bea 4096 Dec 29 19:09 _WL_internal
drwxr----- 3 weblogic bea 4096 Mar 17 17:53 _WL_user
-rw-r----- 1 weblogic bea  264 Apr 21 17:11 WebServiceUtils.ser
-rw-r----- 1 weblogic bea    0 Apr 21 17:24 AdminServer.lok

第二种方法,删除AdminServer.lok文件并重新使用weblogic用户来启动weblogic服务的操作示例如下:
1. 在user_projects/domains/目录中移动或删除它被锁定的文件,例如:

$ find . -name "*DAT"
./servers/AdminServer/data/store/diagnostics/WLS_DIAGNOSTICS000000.DAT
./servers/AdminServer/data/store/default/_WLS_ADMINSERVER000000.DAT

这里要注意的是Unix或Linux是大小敏感的,在执行查找时不要将***221;*DAT***221;写成***221;.dat***221;,如果写成***221;.dat***221;可能删除将?/security/SerializedSystemIni.dat文件删除。

2.删除EmbeddedLDAP.lok and AdminServer.lok 文件

[root@localhost tmp]rm  -rf /home/weblogic/Oracle/Middleware/user_projects/domains/sbkwsjb/servers/AdminServer/tmp/AdminServer.lok


[root@localhost tmp]rm  -rf
/home/weblogic/Oracle/Middleware/user_projects/domains/sbkwsjb/servers/AdminServer/data/ldap/ldapfiles/EmbeddedLDAP.lok

3.可以执行下面的脚本来找出进程正在监听的端口并通过kill -TERM将其杀掉。

#!/bin/ksh

line='---------------------------------------------'
pids=$(/usr/bin/ps -ef | sed 1d | awk '{print $2}')

if [ $# -eq 0 ]; then
   read ans?"Enter port you would like to know pid for: "
else
   ans=$1
fi

for f in $pids
do
   /usr/proc/bin/pfiles $f 2>/dev/null | /usr/xpg4/bin/grep -q "port: $ans"
   if [ $? -eq 0 ]; then
      echo $line
      echo "Port: $ans is being used by PID:\c"
      /usr/bin/ps -ef -o pid -o args | egrep -v "grep|pfiles" | grep $f
   fi
done
exit 0

4.重新启动weblogic服务

[weblogic@localhost bin]$nohup ./startWebLogic.sh &

第三种方法,杀死仍然运行的服务进程并重新weblogic服务的示例操作如下:
1. 找到Managed Server的:

ps -ef | grep ManagedServer

2.使用kill -9 杀掉Managed Server进程:

kill -9 

3. 重新启动weblogic服务:

nohup ./startWebLogic.sh &

要注意的是startWeblogic.sh脚本通常要使用nohup来启动,这样当用户退出时shell才能保持继续运行.

如果是Windows平台
1. AdminServer.lok 文件的缺存储目录是: C:\Oracle\Middleware\user_projects\domains\\servers\AdminServer\tmp\
2. 执行startWebLogic.cmd 命令来启动weblogic服务
注意,如果AdminServer.lok 不能删除可以在任务管理器中杀掉Weblogic服务的所有java.exe进程,再来删除AdminServer.lok file。

在解决不能锁定AdminServer.lok文件之后我们再来使用weblogic用户启动weblogic服务:

[weblogic@localhost bin]$ ./startWebLogic.sh
...省略...
an error occurred while initializing the Embedded LDAP Server. The exception thrown is java.lang.ClassCastException: com
.octetstring.vde.backend.BackendRoot cannot be cast to com.octetstring.vde.backend.standard.BackendStandard. This may indicate a problem with the data files for the Embedded LDAP Server. If
the problem is with the data files and it can not be corrected, backups of previous versions of the data files exist in /home/weblogic/Oracle/Middleware/user_projects/domains/sbkwsjb/servers
/AdminServer/data/ldap/backup
bea -000362 server failed. Reason:

There are 1 nested errors:

java.lang.ClassCastException: com.octetstring.vde.backend.BackendRoot cannot be cast to com.octetstring.vde.backend.standard.BackendStandard
        at weblogic.ldap.EmbeddedLDAP.start(EmbeddedLDAP.java:303)
        at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64)
        at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256)
        at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)

出现的错误是没有权限打开事务文件EmbeddedLDAP.tran,这还是权限原因:

/home/weblogic/Oracle/Middleware/user_projects/domains/sbkwsjb/servers/AdminServer/data/
ldap/ldapfiles/EmbeddedLDAP.tran (Permission denied)

修改EmbeddedLDAP.tran 文件的权限:

[root@localhost weblogic]# cd /home/weblogic/Oracle/Middleware/user_projects/domains/sbkwsjb/servers/AdminServer/data/ldap/ldapfiles/
[root@localhost ldapfiles]# ls -lrt
total 164
-rw-r----- 1 weblogic bea     864 Dec 30 09:01 EmbeddedLDAP.index
-rw-r----- 1 weblogic bea     204 Dec 30 09:01 EmbeddedLDAP.delete
-rw-r----- 1 weblogic bea  146088 Apr 21 16:16 EmbeddedLDAP.data
-rw-r----- 1 weblogic bea       8 Apr 21 17:24 EmbeddedLDAP.twpos
-rw-r----- 1 weblogic bea       8 Apr 21 17:24 EmbeddedLDAP.trpos
-rw-r----- 1 root     root      0 Apr 21 17:24 EmbeddedLDAP.tran
-rw-r----- 1 weblogic bea       0 Apr 21 19:26 EmbeddedLDAP.lok

从上面的信息可以看到EmbeddedLDAP.tran文件的权限是root用户所有,这是因为之前使用root用户启动过weblogic的原因,而现在是使用weblogic用户来启动weblogic

[root@localhost ldapfiles]# chown weblogic EmbeddedLDAP.tran
[root@localhost ldapfiles]# ls -lrt
total 164
-rw-r----- 1 weblogic bea     864 Dec 30 09:01 EmbeddedLDAP.index
-rw-r----- 1 weblogic bea     204 Dec 30 09:01 EmbeddedLDAP.delete
-rw-r----- 1 weblogic bea  146088 Apr 21 16:16 EmbeddedLDAP.data
-rw-r----- 1 weblogic bea       8 Apr 21 17:24 EmbeddedLDAP.twpos
-rw-r----- 1 weblogic bea       8 Apr 21 17:24 EmbeddedLDAP.trpos
-rw-r----- 1 weblogic root      0 Apr 21 17:24 EmbeddedLDAP.tran
-rw-r----- 1 weblogic bea       0 Apr 21 19:26 EmbeddedLDAP.lok
[root@localhost ldapfiles]# chown weblogic:bea EmbeddedLDAP.tran
[root@localhost ldapfiles]# ls -lrt
total 164
-rw-r----- 1 weblogic bea    864 Dec 30 09:01 EmbeddedLDAP.index
-rw-r----- 1 weblogic bea    204 Dec 30 09:01 EmbeddedLDAP.delete
-rw-r----- 1 weblogic bea 146088 Apr 21 16:16 EmbeddedLDAP.data
-rw-r----- 1 weblogic bea      8 Apr 21 17:24 EmbeddedLDAP.twpos
-rw-r----- 1 weblogic bea      8 Apr 21 17:24 EmbeddedLDAP.trpos
-rw-r----- 1 weblogic bea      0 Apr 21 17:24 EmbeddedLDAP.tran
-rw-r----- 1 weblogic bea      0 Apr 21 19:26 EmbeddedLDAP.lok

再次使用weblogic用户来启动weblogic

[weblogic@localhost bin]$ ./startWebLogic.sh
.
.
JAVA Memory arguments: -Xms1024m -Xmx2048m -XX:CompileThreshold=8000 -XX:PermSize=256m  -XX:MaxPermSize=512m
.
WLS Start Mode=Development
.
CLASSPATH=/home/weblogic/Oracle/Middleware/patch_wls1036/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/home/weblogic/Oracle/Middleware/patch_ocp371/profiles/default/sys_manifes
t_classpath/weblogic_patch.jar:/usr/java64/jdk1.6.0_45/lib/tools.jar:/home/weblogic/Oracle/Middleware/wlserver_10.3/server/lib/weblogic_sp.jar:/home/weblogic/Oracle/Middleware/wlserver_10.3/
server/lib/weblogic.jar:/home/weblogic/Oracle/Middleware/modules/features/weblogic.server.modules_10.3.6.0.jar:/home/weblogic/Oracle/Middleware/wlserver_10.3/server/lib/webservices.jar:/home
/weblogic/Oracle/Middleware/modules/org.apache.ant_1.7.1/lib/ant-all.jar:/home/weblogic/Oracle/Middleware/modules/net.sf.antcontrib_1.1.0.0_1-0b2/lib/ant-contrib.jar:/home/weblogic/Oracle/Mi
ddleware/wlserver_10.3/common/derby/lib/derbyclient.jar:/home/weblogic/Oracle/Middleware/wlserver_10.3/server/lib/xqrl.jar:.:/usr/java64/jdk1.6.0_45/lib:/usr/java64/jdk1.6.0_45/lib/dt.jar:/u
sr/java64/jdk1.6.0_45/lib/tools.jar
.
PATH=/home/weblogic/Oracle/Middleware/wlserver_10.3/server/bin:/home/weblogic/Oracle/Middleware/modules/org.apache.ant_1.7.1/bin:/usr/java64/jdk1.6.0_45/jre/bin:/usr/java64/jdk1.6.0_45/bin:/
usr/lib64/qt-3.3/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/java64/jdk1.6.0_45/bin:/home/weblogic/bin
.
***************************************************
*  To start WebLogic Server, use a username and   *
*  password assigned to an admin-level user.  For *
*  server administration, use the WebLogic Server *
*  console at http://hostname:port/console        *
***************************************************
starting weblogic with Java version:
java version "1.6.0_45"
Java(TM) SE Runtime Environment (build 1.6.0_45-b06)
Java HotSpot(TM) 64-Bit Server VM (build 20.45-b01, mixed mode)
Starting WLS with line:
/usr/java64/jdk1.6.0_45/bin/java -client   -Xms1024m -Xmx2048m -XX:CompileThreshold=8000 -XX:PermSize=256m  -XX:MaxPermSize=512m -Dweblogic.Name=AdminServer -Djava.security.policy=/home/webl
ogic/Oracle/Middleware/wlserver_10.3/server/lib/weblogic.policy  -Xverify:none  -da -Dplatform.home=/home/weblogic/Oracle/Middleware/wlserver_10.3 -Dwls.home=/home/weblogic/Oracle/Middleware
/wlserver_10.3/server -Dweblogic.home=/home/weblogic/Oracle/Middleware/wlserver_10.3/server   -Dweblogic.management.discover=true  -Dwlw.iterativeDev= -Dwlw.testConsole= -Dwlw.logErrorsToCon
sole= -Dweblogic.ext.dirs=/home/weblogic/Oracle/Middleware/patch_wls1036/profiles/default/sysext_manifest_classpath:/home/weblogic/Oracle/Middleware/patch_ocp371/profiles/default/sysext_mani
fest_classpath  weblogic.Server

至此问题已经解决。

总结:出现不能锁定AdminServer.lok文件的错误的原因是,因为使用kill -9 杀掉了weblogic服务进程,或者使用不是安装weblogic程序的用户来启动过weblogic所造成的。出现这种问题后,有三种解决方法:
1.是修改AdminServer.lok文件的权限,让weblogic用户能锁定它.
2.删除AdminServer.lok文件并重新使用weblogic用户来启动weblogic服务
3.杀死仍然运行的服务进程并重新weblogic服务.

使用Pipe来执行RMAN命令

RMAN Pipe接口是执行RMAN命令并接收命令输出的一种替代方法。使用这种接口,RMAN使用dbms_pipe包来获得命令并发送输出来代替操作系统shell。使用这种接口,可以编写可移值的RMAN程序接口。这个功能使用我们中心自己开发的数据库监控平台可以调用RMAN来执行备份,使用pipe接口编写RMAN备份脚本可以跨操作系统平台使用。
pipe接口是通过使用pipe命令行参数来进行调用的。RMAN使用两种私有管道:一个用来接收命令,一个用来发送输出。管道名是用pipe参数来决定的。例如,执行下面的命令 :

[oracle11@jingyong1 ~]$ rman PIPE abc TARGET /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 17 10:38:53 2015

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

通过管道连接执行多个RMAN命令
假设想要通过管道来连续执行多个命令。在每一个命令发送到管道执行并返回输出后,RMAN会暂停并等待下一个命令。

1.以pipe选项来启动RMAN并连接到目标数据库:

[oracle11@jingyong1 ~]$ rman PIPE abc TARGET /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 17 10:38:53 2015

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

也可以指定timeout选项,强制RMAN在指定的时间内没有从输入管道接收到命令就自动退出。

[oracle11@oracle11g ~]$ rman PIPE abc TARGET / TIMEOUT 60

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 17 14:51:12 2015

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

[oracle11@oracle11g ~]$ date
Fri Apr 17 14:52:14 CST 2015

2.连接管目标数据库并通过dbms_pipe.pack_message与dbms_pipe.send_message在输入管道中输入要执行的命令。当RMAN使用管道来代替标准RMAN输入时会显示消息RMAN-00572,下面使用管道来显示当前的RMAN配置信息并对数据库执行备份。

[oracle11@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 17 11:49:25 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serverout on size 100000
SQL> declare
  2  i_v varchar2(2000):='show all;';
  3  o_v number;
  4  begin
  5  dbms_pipe.pack_message(i_v);
  6  o_v:=dbms_pipe.send_message('ORA$RMAN_ABC_IN');
  7  dbms_output.put_line(o_v);
  8  commit;
  9  end;
 10  /
0

PL/SQL procedure successfully completed.




[oracle11@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 17 11:49:30 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serverout on size 100000
SQL> declare
  2  i_v varchar2(2000):='backup as backupset database plus archivelog;';
  3  o_v number;
  4  begin
  5  dbms_pipe.pack_message(i_v);
  6  o_v:=dbms_pipe.send_message('ORA$RMAN_ABC_IN');
  7  dbms_output.put_line(o_v);
  8  commit;
  9  end;
 10  /
0

PL/SQL procedure successfully completed.

3.使用dbms_pipe.receive_message和dbms_pipe.unpack_message来读取RMAN的输出信息:

SQL> declare
  2  output_v varchar2(4000);
  3  o_v number:=0;
  4  begin
  5  while(o_v=0) loop
  6   o_v:=dbms_pipe.receive_message('ORA$RMAN_ABC_OUT',5);
  7   if o_v=0 then
  8    dbms_pipe.unpack_message(output_v);
  9     dbms_output.put_line(output_v);
 10   end if;
 11  end loop;
 12  commit;
 13  end;
 14  /
connected to target database: DB (DBID=1640573015)
RMAN-00572: waiting for DBMS_PIPE input
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR
LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/u03/app/oracle/11.2.0/db/dbs/snapcf_db.f'; # default
RMAN-00572: waiting for DBMS_PIPE input

PL/SQL procedure successfully completed.

SQL> declare
  2  output_v varchar2(4000);
  3  o_v number:=0;
  4  begin
  5  while(o_v=0) loop
  6   o_v:=dbms_pipe.receive_message('ORA$RMAN_ABC_OUT',600);
  7   if o_v=0 then
  8    dbms_pipe.unpack_message(output_v);
  9     dbms_output.put_line(output_v);
 10   end if;
 11  end loop;
 12  commit;
 13  end;
 14  /
Starting backup at 2015-04-17 12:21:43
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=18 RECID=11 STAMP=877175019
input archived log thread=1 sequence=19 RECID=12 STAMP=877175163
input archived log thread=1 sequence=20 RECID=13 STAMP=877212065
input archived log thread=1 sequence=21 RECID=14 STAMP=877253311
input archived log thread=1 sequence=22 RECID=15 STAMP=877262007
input archived log thread=1 sequence=23 RECID=16 STAMP=877262176
input archived log thread=1 sequence=24 RECID=17 STAMP=877262429
input archived log thread=1 sequence=25 RECID=18 STAMP=877262605
input archived log thread=1 sequence=26 RECID=19 STAMP=877262607
input archived log thread=1 sequence=27 RECID=20 STAMP=877262777
input archived log thread=1 sequence=28 RECID=21 STAMP=877263703
channel ORA_DISK_1: starting piece 1 at 2015-04-17 12:21:44
channel ORA_DISK_1: finished piece 1 at 2015-04-17 12:21:59
piece handle=/u03/app/oracle/11.2.0/db/dbs/1fq4juqn_1_1 tag=TAG20150417T122143
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2015-04-17 12:21:59
Starting backup at 2015-04-17 12:21:59
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/db/example01.dbf
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-04-17 12:21:59
channel ORA_DISK_1: finished piece 1 at 2015-04-17 12:24:34
piece handle=/u03/app/oracle/11.2.0/db/dbs/1gq4jur7_1_1 tag=TAG20150417T122159
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:36
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2015-04-17 12:24:42
channel ORA_DISK_1: finished piece 1 at 2015-04-17 12:24:43
piece handle=/u03/app/oracle/11.2.0/db/dbs/1hq4jv03_1_1 tag=TAG20150417T122159
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-04-17 12:24:43
Starting backup at 2015-04-17 12:24:43
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=29 RECID=22 STAMP=877263883
channel ORA_DISK_1: starting piece 1 at 2015-04-17 12:24:44
channel ORA_DISK_1: finished piece 1 at 2015-04-17 12:24:45
piece handle=/u03/app/oracle/11.2.0/db/dbs/1iq4jv0c_1_1 tag=TAG20150417T122444
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-04-17 12:24:45
RMAN-00572: waiting for DBMS_PIPE input
Starting backup at 2015-04-17 12:26:09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=18 RECID=11 STAMP=877175019
input archived log thread=1 sequence=19 RECID=12 STAMP=877175163
input archived log thread=1 sequence=20 RECID=13 STAMP=877212065
input archived log thread=1 sequence=21 RECID=14 STAMP=877253311
input archived log thread=1 sequence=22 RECID=15 STAMP=877262007
input archived log thread=1 sequence=23 RECID=16 STAMP=877262176
input archived log thread=1 sequence=24 RECID=17 STAMP=877262429
input archived log thread=1 sequence=25 RECID=18 STAMP=877262605
input archived log thread=1 sequence=26 RECID=19 STAMP=877262607
input archived log thread=1 sequence=27 RECID=20 STAMP=877262777
input archived log thread=1 sequence=28 RECID=21 STAMP=877263703
input archived log thread=1 sequence=29 RECID=22 STAMP=877263883
input archived log thread=1 sequence=30 RECID=23 STAMP=877263969
channel ORA_DISK_1: starting piece 1 at 2015-04-17 12:26:10
channel ORA_DISK_1: finished piece 1 at 2015-04-17 12:26:25
piece handle=/u03/app/oracle/11.2.0/db/dbs/1jq4jv32_1_1 tag=TAG20150417T122610
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2015-04-17 12:26:25
Starting backup at 2015-04-17 12:26:25
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/db/example01.dbf
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-04-17 12:26:26
channel ORA_DISK_1: finished piece 1 at 2015-04-17 12:29:21
piece handle=/u03/app/oracle/11.2.0/db/dbs/1kq4jv3i_1_1 tag=TAG20150417T122625
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2015-04-17 12:29:24
channel ORA_DISK_1: finished piece 1 at 2015-04-17 12:29:27
piece handle=/u03/app/oracle/11.2.0/db/dbs/1lq4jv91_1_1 tag=TAG20150417T122625
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2015-04-17 12:29:27
Starting backup at 2015-04-17 12:29:27
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=31 RECID=24 STAMP=877264168
channel ORA_DISK_1: starting piece 1 at 2015-04-17 12:29:28
channel ORA_DISK_1: finished piece 1 at 2015-04-17 12:29:29
piece handle=/u03/app/oracle/11.2.0/db/dbs/1mq4jv98_1_1 tag=TAG20150417T122928
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-04-17 12:29:29
RMAN-00572: waiting for DBMS_PIPE input

PL/SQL procedure successfully completed.

4.如果启动RMAN时使用了timeout选项,那么RMAN在指定时间间隔内没有接收到任何命令就会自动终止RMAN会话。为了强制RMAN立即终止RMAN会话可以发送exit命令。

SQL> declare
  2  i_v varchar2(2000):='exit;';
  3  o_v number;
  4  begin
  5  dbms_pipe.pack_message(i_v);
  6  o_v:=dbms_pipe.send_message('ORA$RMAN_ABC_IN');
  7  dbms_output.put_line(o_v);
  8  commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> declare
  2  output_v varchar2(4000);
  3  o_v number:=0;
  4  begin
  5  while(o_v=0) loop
  6   o_v:=dbms_pipe.receive_message('ORA$RMAN_ABC_OUT',10);--10是超过时间为10秒
  7   if o_v=0 then
  8    dbms_pipe.unpack_message(output_v);
  9     dbms_output.put_line(output_v);
 10   end if;
 11  end loop;
 12  commit;
 13  end;
 14  /
Recovery Manager complete.
PL/SQL procedure successfully completed.

对删除分区的分区表执行TSPITR

在有些情况下将分表区中的一些分区删除后需要恢复,但又不是对整个分区表恢复到某个时间点,这可以通过辅助数据库执行TSPITR来实现,下面的例子介绍了使用这种方法的具体操作过程。
1.对每个要执行恢复的分区在主数据库中创建一个表,分区表是sales,有28个分区,删除分区sales_1995,sales_1996之后对分区表sales执行TSPITR来恢复分区sales_1995,sales_1996

SQL> select a.owner,a.table_name,a.partitioning_type,a.subpartitioning_type,a.partition_count,a.def_tablespace_name from dba_part_tables a where a.owner='TEST' and a.table_name='SALES';

OWNER                          TABLE_NAME                     PARTITIONING_TYPE SUBPARTITIONING_TYPE PARTITION_COUNT DEF_TABLESPACE_NAME
------------------------------ ------------------------------ ----------------- -------------------- --------------- ------------------------------
TEST                           SALES                          RANGE             NONE                              28 TEST


SQL> select a.table_owner,a.table_name,a.partition_name from dba_tab_partitions a where a.table_owner='TEST' and a.table_name='SALES';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           SALES                          SALES_1995
TEST                           SALES                          SALES_1996
TEST                           SALES                          SALES_H1_1997
TEST                           SALES                          SALES_H2_1997
TEST                           SALES                          SALES_Q1_1998
TEST                           SALES                          SALES_Q2_1998
TEST                           SALES                          SALES_Q3_1998
TEST                           SALES                          SALES_Q4_1998
TEST                           SALES                          SALES_Q1_1999
TEST                           SALES                          SALES_Q2_1999
TEST                           SALES                          SALES_Q3_1999
TEST                           SALES                          SALES_Q4_1999
TEST                           SALES                          SALES_Q1_2000
TEST                           SALES                          SALES_Q2_2000
TEST                           SALES                          SALES_Q3_2000
TEST                           SALES                          SALES_Q4_2000
TEST                           SALES                          SALES_Q1_2001
TEST                           SALES                          SALES_Q2_2001
TEST                           SALES                          SALES_Q3_2001
TEST                           SALES                          SALES_Q4_2001
TEST                           SALES                          SALES_Q1_2002
TEST                           SALES                          SALES_Q2_2002
TEST                           SALES                          SALES_Q3_2002
TEST                           SALES                          SALES_Q4_2002
TEST                           SALES                          SALES_Q1_2003
TEST                           SALES                          SALES_Q2_2003
TEST                           SALES                          SALES_Q3_2003
TEST                           SALES                          SALES_Q4_2003

对主数据库进行备份

SQL> alter database begin backup;

Database altered.
[oracle@oracle11g backup]$ cp /u01/app/oracle/oradata/test/*.dbf /u02/backup/

SQL> alter database end backup;

Database altered.

SQL> alter database backup controlfile to '/u02/backup/control.ctl';

Database altered.

SQL> alter system switch logfile;

System altered.

2.删除分区表sales上的sales_1995,sales_1996

SQL> select current_scn,to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;

CURRENT_SCN TO_CHAR(SCN_TO_TIME
----------- -------------------
     430881 2015-04-10 20:56:59

在执行前记录当前时间,在对辅助数据库进行恢复时这就是恢复的目标时间点

SQL> alter table test.sales drop partition sales_1995;

Table altered.

SQL> alter table test.sales drop partition sales_1996;

Table altered.

SQL> select count(*) from test.sales partition(SALES_1995);
select count(*) from test.sales partition(SALES_1995)
                                          *
ERROR at line 1:
ORA-02149: Specified partition does not exist


SQL> select count(*) from test.sales partition(SALES_1996);
select count(*) from test.sales partition(SALES_1996)
                                          *
ERROR at line 1:
ORA-02149: Specified partition does not exist


SQL> select count(*) from test.sales  where time_id
3.创建辅助数据库
将辅助集和恢复集表空间的数据文件与备份的控制文件 还原到/u02/auxiliary目录中
[oracle@oracle11g backup]$ cp system01.dbf  /u02/auxiliary/
[oracle@oracle11g backup]$ cp sysaux01.dbf /u02/auxiliary/
[oracle@oracle11g backup]$ cp undotbs01.dbf /u02/auxiliary/
[oracle@oracle11g backup]$ cp test01.dbf /u02/auxiliary/
[oracle@oracle11g backup]$ cp temp01.dbf /u02/auxiliary/
[oracle@oracle11g backup]$ cp control.ctl /u02/auxiliary/control01.ctl
[oracle@oracle11g backup]$ cp control.ctl /u02/auxiliary/control02.ctl
[oracle@oracle11g backup]$ cp control.ctl /u02/auxiliary/control03.ctl
[oracle@oracle11g backup]$ cp /u02/archive/* /u02/backup/
[oracle@oracle11g backup]$ cp /u01/app/oracle/oradata/test/*.log /u02/backup/

创建初始化参数文件

[oracle@oracle11g auxiliary]$ vi initauxiliary.ora

db_name=test
db_unique_name=auxiliary
sga_max_size=160M
sga_target=160M
pga_aggregate_target=16M
db_file_name_convert=('/u01/app/oracle/oradata/test/','/u02/auxiliary/')
log_file_name_convert=('/u01/app/oracle/oradata/test/','/u02/auxiliary/')
control_files=('/u02/auxiliary/control01.ctl','/u02/auxiliary/control02.ctl','/u02/auxiliary/control03.ctl')
log_archive_dest_1='location=/u02/backup'
log_archive_format='%t_%s_%r.dbf'
compatible=10.2.0.5.0

还原和恢复辅助实例

[oracle@oracle11g ~]$ export ORACLE_SID=auxiliary
[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Apr 10 21:05:42 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/u02/auxiliary/initauxiliary.ora'
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> alter database mount clone database;

Database altered.

SQL> SELECT NAME FROM V$DATAFILE
  2  UNION ALL
  3  SELECT MEMBER FROM V$LOGFILE
  4  UNION ALL
  5  SELECT NAME FROM V$CONTROLFILE
  6  union all
  7  select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u02/auxiliary/system01.dbf
/u02/auxiliary/undotbs01.dbf
/u02/auxiliary/sysaux01.dbf
/u02/auxiliary/users01.dbf
/u02/auxiliary/example01.dbf
/u02/auxiliary/test01.dbf
/u02/auxiliary/redo03.log
/u02/auxiliary/redo02.log
/u02/auxiliary/redo01.log
/u02/auxiliary/control01.ctl
/u02/auxiliary/control02.ctl

NAME
--------------------------------------------------------------------------------
/u02/auxiliary/control03.ctl
/u02/auxiliary/temp01.dbf

13 rows selected.

SQL> alter database datafile '/u02/auxiliary/system01.dbf' online;

Database altered.

SQL> alter database datafile '/u02/auxiliary/undotbs01.dbf' online;

Database altered.

SQL> alter database datafile '/u02/auxiliary/test01.dbf' online;

Database altered.

SQL> alter database datafile '/u02/auxiliary/sysaux01.dbf' online;

Database altered.

SQL> alter database tempfile '/u02/auxiliary/temp01.dbf' online;

Database altered.

SQL> recover  database  until  time  '2015-04-10 20:56:59'  using  backup  controlfile;
ORA-00279: change 430764 generated at 04/10/2015 20:52:21 needed for thread 1
ORA-00289: suggestion : /u02/backup/1_7_876665479.dbf
ORA-00280: change 430764 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 430825 generated at 04/10/2015 20:54:32 needed for thread 1
ORA-00289: suggestion : /u02/backup/1_8_876665479.dbf
ORA-00280: change 430825 for thread 1 is in sequence #8
ORA-00278: log file '/u02/backup/1_7_876665479.dbf' no longer needed for this
recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from test.sales partition(SALES_1995);

  COUNT(*)
----------
       999

SQL> select count(*) from test.sales partition(SALES_1996);

  COUNT(*)
----------
       999


从上面的结果可以看到我们将辅助数据库恢复到删除分区之前的时间点了。

4.在辅助数据库中创建与分区进行交换相关的表,而且交换表只能创建在SYSTEM表空间中,并且与分区表有完全相同的列名和数据类型。如果交换表没有创建在SYSTEM表空间中会出现ORA-01552错误。

SQL> create table test.tts_sales_1995 tablespace system  as select * from test.sales partition(SALES_1995) where 1=2;

Table created.

SQL> create table test.tts_sales_1996 tablespace system as select * from test.sales partition(SALES_1996) where 1=2;

Table created.

5.在辅助数据库上使用表tts_sales_1995,tts_sales_1996与分区sales_1995,sales_1996进行交换

SQL> alter table test.sales  exchange partition sales_1995 with table test.tts_sales_1995;

Table altered.

SQL> alter table test.sales exchange partition sales_1996 with table test.tts_sales_1996;

Table altered.

SQL> select count(*) from test.tts_sales_1995;

  COUNT(*)
----------
       999

SQL> select count(*) from test.tts_sales_1996;

  COUNT(*)
----------
       999

SQL> select count(*) from test.sales partition(SALES_1995);

  COUNT(*)
----------
         0

SQL> select count(*) from test.sales partition(SALES_1996);

  COUNT(*)
----------
         0

6.导出交换表tts_sales_1995,tts_sales_1996

[oracle@oracle11g auxiliary]$ export ORACLE_SID=auxiliary
[oracle@oracle11g auxiliary]$ exp \'test/test\' file=/u02/sales.dmp log=/u02/sales.log tables=\(tts_sales_1995,tts_sales_1996\)

Export: Release 10.2.0.5.0 - Production on Fri Apr 10 21:16:32 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

About to export specified tables via Conventional Path ...
. . exporting table                 TTS_SALES_1995        999 rows exported
. . exporting table                 TTS_SALES_1996        999 rows exported
Export terminated successfully without warnings.

7.将交换表 tts_sales_1995,tts_sales_1996导入到主数据库中

[oracle@oracle11g auxiliary]$ export ORACLE_SID=test
[oracle@oracle11g auxiliary]$ imp \'test/test\' file=/u02/sales.dmp log=/u02/sales_dr.log fromuser=test touser=test

Import: Release 10.2.0.5.0 - Production on Fri Apr 10 21:17:20 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
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. . importing table               "TTS_SALES_1995"        999 rows imported
. . importing table               "TTS_SALES_1996"        999 rows imported
Import terminated successfully without warnings.

8.对主数据库中的分区表sales增加两个分区sales_1995,sales_1996

SQL> alter table test.sales  add partition sales_1995 values less than(TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
alter table test.sales  add partition sales_1995 values less than(TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
                                      *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition


SQL> alter table test.sales  split partition sales_h1_1997 at(TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  2  into  (partition sales_1995,partition sales_h1_1997);

Table altered.

SQL> alter table test.sales  split partition sales_h1_1997 at(TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  2  into  (partition sales_1996,partition sales_h1_1997);

Table altered.

9.使用表tts_sales_1995,tts_sales_1996与主数据库中的表sales的分区sales_1995,sales_1996进行交换

SQL> alter table test.sales  exchange partition sales_1995 with table test.tts_sales_1995;

Table altered.

SQL> alter table test.sales exchange partition sales_1996 with table test.tts_sales_1996;

Table altered.

SQL> select count(*) from test.tts_sales_1995;

  COUNT(*)
----------
         0

SQL> select count(*) from test.tts_sales_1996;

  COUNT(*)
----------
         0



SQL> select count(*) from test.sales partition(SALES_1995);

  COUNT(*)
----------
       999

SQL> select count(*) from test.sales partition(SALES_1996);

  COUNT(*)
----------
       999

从上面的结果可以看到,表sales的sales_1995,sales_1996两个分区的数据恢复回来,对主数据库进行备份这里不再赘述。

对分区表的部分分区执行TSPITR

分区表可以跨多个表空间,Oracle允许对部分分区执行TSPITR而不是所有分区。
1.对每个要执行恢复的分区在主数据库中创建一个表,分区表是sales,有28个分区,要执行TSPITR的分区是sales_1995,sales_1996,所以对这两个分区创建两个单独的表sales_1995,sales_1996

SQL> select a.owner,a.table_name,a.partitioning_type,a.subpartitioning_type,a.partition_count,a.def_tablespace_name from dba_part_tables a where a.owner='TEST' and a.table_name='SALES';

OWNER                          TABLE_NAME                     PARTITIONING_TYPE SUBPARTITIONING_TYPE PARTITION_COUNT DEF_TABLESPACE_NAME
------------------------------ ------------------------------ ----------------- -------------------- --------------- ------------------------------
TEST                           SALES                          RANGE             NONE                              28 TEST


SQL> select a.table_owner,a.table_name,a.partition_name from dba_tab_partitions a where a.table_owner='TEST' and a.table_name='SALES';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           SALES                          SALES_1995
TEST                           SALES                          SALES_1996
TEST                           SALES                          SALES_H1_1997
TEST                           SALES                          SALES_H2_1997
TEST                           SALES                          SALES_Q1_1998
TEST                           SALES                          SALES_Q2_1998
TEST                           SALES                          SALES_Q3_1998
TEST                           SALES                          SALES_Q4_1998
TEST                           SALES                          SALES_Q1_1999
TEST                           SALES                          SALES_Q2_1999
TEST                           SALES                          SALES_Q3_1999
TEST                           SALES                          SALES_Q4_1999
TEST                           SALES                          SALES_Q1_2000
TEST                           SALES                          SALES_Q2_2000
TEST                           SALES                          SALES_Q3_2000
TEST                           SALES                          SALES_Q4_2000
TEST                           SALES                          SALES_Q1_2001
TEST                           SALES                          SALES_Q2_2001
TEST                           SALES                          SALES_Q3_2001
TEST                           SALES                          SALES_Q4_2001
TEST                           SALES                          SALES_Q1_2002
TEST                           SALES                          SALES_Q2_2002
TEST                           SALES                          SALES_Q3_2002
TEST                           SALES                          SALES_Q4_2002
TEST                           SALES                          SALES_Q1_2003
TEST                           SALES                          SALES_Q2_2003
TEST                           SALES                          SALES_Q3_2003
TEST                           SALES                          SALES_Q4_2003


SQL> create table test.sales_1995 as select * from test.sales where 1=2;

Table created.

SQL> create table test.sales_1996 as select * from test.sales where 1=2;

Table created.

对主数据库进行备份

SQL> alter database begin backup;

Database altered.
[oracle@oracle11g backup]$ cp /u01/app/oracle/oradata/test/*.dbf /u02/backup/

SQL> alter database end backup;

Database altered.

SQL> alter database backup controlfile to '/u02/backup/control.ctl';

Database altered.

SQL> alter system switch logfile;

System altered.

2.删除要执行TSPITR的分区上的索引(这里是创建的本地索引)

SQL> select current_scn,to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;

CURRENT_SCN TO_CHAR(SCN_TO_TIME
----------- -------------------
     425540 2015-04-10 17:38:52



在执行前记录当前时间,在对辅助数据库进行恢复时这就是恢复的目标时间点

SQL> alter index  TEST.SALES_CUST_BIX modify partition SALES_1995 unusable;

Index altered.

SQL> alter index  TEST.SALES_CUST_BIX modify partition SALES_1996 unusable;

Index altered.

3.交换分区表
每一个要执行TSPITR的分区与其相关的表进行分区交换

SQL> alter table test.sales exchange partition sales_1995 with table test.sales_1995;

Table altered.

SQL> alter table test.sales exchange partition sales_1996 with table test.sales_1996;

Table altered.

SQL> select count(*) from test.sales partition(SALES_1995);

  COUNT(*)
----------
         0

SQL> select count(*) from test.sales partition(SALES_1996);

  COUNT(*)
----------
         0

SQL> select count(*) from test.sales_1995;

  COUNT(*)
----------
       999

SQL> select count(*) from test.sales_1996;

  COUNT(*)
----------
       999

5.创建辅助数据库
将辅助集和恢复集表空间的数据文件与备份的控制文件 还原到/u02/auxiliary目录中

[oracle@oracle11g backup]$ cp system01.dbf  /u02/auxiliary/
[oracle@oracle11g backup]$ cp sysaux01.dbf /u02/auxiliary/
[oracle@oracle11g backup]$ cp undotbs01.dbf /u02/auxiliary/
[oracle@oracle11g backup]$ cp test01.dbf /u02/auxiliary/
[oracle@oracle11g backup]$ cp temp01.dbf /u02/auxiliary/
[oracle@oracle11g backup]$ cp control.ctl /u02/auxiliary/control01.ctl
[oracle@oracle11g backup]$ cp control.ctl /u02/auxiliary/control02.ctl
[oracle@oracle11g backup]$ cp control.ctl /u02/auxiliary/control03.ctl
[oracle@oracle11g backup]$ cp /u02/archive/* /u02/backup/
[oracle@oracle11g backup]$ cp /u01/app/oracle/oradata/test/*.log /u02/backup/

创建初始化参数文件

[oracle@oracle11g auxiliary]$ vi initauxiliary.ora

db_name=test
db_unique_name=auxiliary
sga_max_size=160M
sga_target=160M
pga_aggregate_target=16M
db_file_name_convert=('/u01/app/oracle/oradata/test/','/u02/auxiliary/')
log_file_name_convert=('/u01/app/oracle/oradata/test/','/u02/auxiliary/')
control_files=('/u02/auxiliary/control01.ctl','/u02/auxiliary/control02.ctl','/u02/auxiliary/control03.ctl')
log_archive_dest_1='location=/u02/backup'
log_archive_format='%t_%s_%r.dbf'
compatible=10.2.0.5.0

还原与恢复辅助实例

[oracle@oracle11g ~]$ export ORACLE_SID=auxiliary
[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Apr 10 15:40:51 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/u02/auxiliary/initauxiliary.ora'
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> alter database mount clone database;

Database altered.
SQL> SELECT NAME FROM V$DATAFILE
  2  UNION ALL
  3  SELECT MEMBER FROM V$LOGFILE
  4  UNION ALL
  5  SELECT NAME FROM V$CONTROLFILE;

NAME
--------------------------------------------------------------------------------
/u02/auxiliary/system01.dbf
/u02/auxiliary/undotbs01.dbf
/u02/auxiliary/sysaux01.dbf
/u02/auxiliary/users01.dbf
/u02/auxiliary/example01.dbf
/u02/auxiliary/test01.dbf
/u02/auxiliary/redo03.log
/u02/auxiliary/redo02.log
/u02/auxiliary/redo01.log
/u02/auxiliary/control01.ctl
/u02/auxiliary/control02.ctl
/u02/auxiliary/control03.ctl

12 rows selected.

SQL> alter database datafile '/u02/auxiliary/system01.dbf' online;

Database altered.

SQL> alter database datafile '/u02/auxiliary/undotbs01.dbf' online;

Database altered.

SQL> alter database datafile '/u02/auxiliary/test01.dbf' online;

Database altered.


SQL> alter database datafile '/u02/auxiliary/sysaux01.dbf' online;

Database altered.


SQL>

Database altered.



SQL> recover  database  until  time  '2015-04-10 17:38:52'  using  backup  controlfile;
ORA-00279: change 425356 generated at 04/10/2015 17:31:13 needed for thread 1
ORA-00289: suggestion : /u02/backup/1_6_876665479.dbf
ORA-00280: change 425356 for thread 1 is in sequence #6


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 425511 generated at 04/10/2015 17:37:22 needed for thread 1
ORA-00289: suggestion : /u02/backup/1_7_876665479.dbf
ORA-00280: change 425511 for thread 1 is in sequence #7
ORA-00278: log file '/u02/backup/1_6_876665479.dbf' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/redo01.log
Log applied.
Media recovery complete.
SQL> alter database  open resetlogs;

Database altered.

SQL> select count(*) from test.sales_1995;

  COUNT(*)
----------
         0

SQL> select count(*) from test.sales_1996;

  COUNT(*)
----------
         0

SQL> select count(*) from test.sales partition(SALES_1995);

  COUNT(*)
----------
       999

SQL> select count(*) from test.sales partition(SALES_1996);

  COUNT(*)
----------
       999

从上面的结果可以看到我们将辅助数据库恢复到执行交换分区之前了。

6.在辅助数据库中创建与分区进行交换相关的表,而且交换表只能创建在SYSTEM表空间中,并且与分区表有完全相同的列名和数据类型。如果交换表没有创建在SYSTEM表空间中会出现ORA-01552错误

SQL> create table test.tts_sales_1995 tablespace system  as select * from test.sales partition(SALES_1995) where 1=2;

Table created.

SQL> create table test.tts_sales_1996 tablespace system as select * from test.sales partition(SALES_1996) where 1=2;

Table created.

SQL> select count(*) from test.tts_sales_1995;

  COUNT(*)
----------
         0

SQL> select count(*) from test.tts_sales_1996;

  COUNT(*)
----------
         0

7.将辅助数据库中的分区表的sales_1995,sales_1996分区上的索引删除(这里是创建的本地索引)

SQL> alter index  SALES_CUST_BIX modify partition SALES_1995 unusable;

Index altered.

SQL> alter index  SALES_CUST_BIX modify partition SALES_1996 unusable;

Index altered.

8.在辅助数据库上使用表tts_sales_1995,tts_sales_1996与分区sales_1995,sales_1996进行交换

SQL> alter table test.sales  exchange partition sales_1995 with table test.tts_sales_1995;

Table altered.

SQL> alter table test.sales exchange partition sales_1996 with table test.tts_sales_1996;

Table altered.

SQL> select count(*) from test.tts_sales_1995;

  COUNT(*)
----------
       999

SQL> select count(*) from test.tts_sales_1996;

  COUNT(*)
----------
       999

SQL> select count(*) from test.sales partition(SALES_1995);

  COUNT(*)
----------
         0

SQL> select count(*) from test.sales partition(SALES_1996);

  COUNT(*)
----------
         0

9.导出交换表tts_sales_1995,tts_sales_1996

SQL> create temporary tablespace test_temp tempfile '/u02/auxiliary/test_temp01.dbf' size 50M;

Tablespace created.

SQL>  alter database default temporary tablespace test_temp;

Database altered.




SQL> alter tablespace test read only;

Tablespace altered.


[oracle@oracle11g ~]$ export ORACLE_SID=auxiliary
[oracle@oracle11g auxiliary]$ exp \'test/test\' file=/u02/sales.dmp log=/u02/sales.log tables=\(tts_sales_1995,tts_sales_1996\)

Export: Release 10.2.0.5.0 - Production on Fri Apr 10 19:10:09 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

About to export specified tables via Conventional Path ...
. . exporting table                 TTS_SALES_1995        999 rows exported
. . exporting table                 TTS_SALES_1996        999 rows exported
Export terminated successfully without warnings.

10.将交换表 tts_sales_1995,tts_sales_1996导入到主数据库中

[oracle@oracle11g auxiliary]$ export ORACLE_SID=test
[oracle@oracle11g auxiliary]$ imp \'test/test\' file=/u02/sales.dmp log=/u02/sales_dr.log fromuser=test touser=test

Import: Release 10.2.0.5.0 - Production on Fri Apr 10 19:12:01 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
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. . importing table               "TTS_SALES_1995"        999 rows imported
. . importing table               "TTS_SALES_1996"        999 rows imported
Import terminated successfully without warnings.

11.使用表tts_sales_1995,tts_sales_1996与主数据库中的表sales的分区sales_1995,sales_1996进行交换

SQL> alter table test.sales  exchange partition sales_1995 with table test.tts_sales_1995;

Table altered.

SQL> alter table test.sales exchange partition sales_1996 with table test.tts_sales_1996;

Table altered.

SQL> select count(*) from test.tts_sales_1995;

  COUNT(*)
----------
         0

SQL> select count(*) from test.tts_sales_1996;

  COUNT(*)
----------
         0



SQL> select count(*) from test.sales partition(SALES_1995);

  COUNT(*)
----------
       999

SQL> select count(*) from test.sales partition(SALES_1996);

  COUNT(*)
----------
       999

从上面的结果可以看到,表sales的sales_1995,sales_1996两个分区的数据恢复回来,最后需要对对主数据库进行备份这里不再赘述。

Proudly powered by WordPress | Indrajeet by Sus Hill.