使用传输表空间执行用户管理的TSPITR

用户管理的表空间按时间点恢复(TSPITR)是使用传输表空间来快速恢复一个或多个表空间到之前某个时间点。在以下情况下使用用户管理的表空间按时间点恢复很有用:
1.当错误执行drop tablespace操作
2.错误的作业或DML语句只对数据库的一部分产生影响
3.在一个物理数据库中有多个方案使用不同的表空间当一个方案要恢复到与其它方案不相同的时间点
4.当表空间在一个大数据库中(VLDB)表空间按时间点恢复要比使用备份还原和恢复整个数据库更有效

一种快速跨数据库传输表空间的方法是从源数据库卸载并且目标数据库中附加表空间。数据库可以跨平台,例如,Solaris和Windows 2000。卸载和附加是通过导出和导入工具来完成的。但并不是实际导出和导入表数据,只是简单的导出和导入内部元数据。

表空间按时间点恢复(TSPITR)的方法
在Oracle9i之前,可以有两种方法来执行用户管理的表空间按时间点恢复:
.传统用户管理的表空间按时间点恢复要求你创建一个克隆数据库。
.使用传输表空间执行用户管理的表空间按时间点恢复

在Oracle10g中,表空间按时间点恢复应该使用传输表空间来完成。传输表空间与传统的方法相比更简单易用。

TSPITR(表空间按时间点恢复)是通过从主数据库中删除要被恢复的表空间,还原一个副本数据库叫作辅助数据库并将其恢复到目标时间点,然后从辅助数据库中将相关的表空间传输到主数据库中.为了便于使用,强烈建议你将辅助数据库和主数据库放在不同的主机上。也还可以在相同主机上执行TSPITR

执行用户管理的表空间按时间点恢复的基本过程如下:
1.将需要执行表空间按时间点恢复的表空间脱机
2.对辅助数据库进行前期设置
3.创建辅助数据库并将其恢复到目标时间点
4.从主数据库中删除要执行表空间按时间点恢复的表空间
5.使用传输表侬间将传输表空间集从辅助数据库传输到主数据库

用户管理的表空间按时间点恢复的准备工作的基本步骤
1.回顾表空间按时间点恢复的要求
在执行TSPITR之前要满足以下条件:
.确保对恢复和辅助集表空间的所有数据文件创建了备份。数据文件备份要在恢复目标时间之前创建
.确保有控制文件备份辅助数据库可以使用。控制文件备份必须满足这些条件:
–控制文件必须是在恢复目标时间之间进行备份的
–控制文件必须使用下面的语句进行备份
alter database backup controlfile to ‘cf_name’;
.确保组成恢复集的所有文件都在辅助数据库的恢复集中,否则在传输表空间的导出步骤阶段会失败。
.在辅助主机上给辅助数据库分配足够的磁盘空间
.提供足够的物理内存启动辅助实例
.如果要被恢复的表空间已经被重命名,确保TSPITR的目标SCN在表空间重命名之后。如果恢复目标时间在重命名之前可以执行数据库按时间点恢复。

2.识别恢复和辅助集表空间的所有数据文件
在你创建辅助数据库之前,连接到主数据库并获得关于主数据库的以下所有信息:
.恢复集表空间所有数据文件的文件名
.system表空间的数据文件名
.undo表空间或包含回滚段表空间的数据文件名
.控制文件名
执行下面的语句可以查询所有数据文件和控制文件名

SQL> SELECT NAME FROM V$DATAFILE
  2  UNION ALL
  3  SELECT NAME FROM V$CONTROLFILE;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/system01.dbf
/u01/app/oracle/oradata/test/undotbs01.dbf
/u01/app/oracle/oradata/test/sysaux01.dbf
/u01/app/oracle/oradata/test/users01.dbf
/u01/app/oracle/oradata/test/example01.dbf
/u01/app/oracle/oradata/test/tspitr01.dbf
/u01/app/oracle/oradata/test/test01.dbf
/u01/app/oracle/oradata/test/jy01.dbf
/u01/app/oracle/oradata/test/control01.ctl
/u01/app/oracle/oradata/test/control02.ctl
/u01/app/oracle/oradata/test/control03.ctl

为了判断system和恢复集表空间的数据文件名可以执行类似于下面的语句:

SQL> SELECT t.NAME AS "reco_tbs", d.NAME AS "dbf_name"
  2  FROM V$DATAFILE d, V$TABLESPACE t
  3  WHERE t.TS# = d.TS#
  4  AND t.NAME IN ('SYSTEM', 'TSPITR', 'TEST');

reco_tbs                           dbf_name
------------------------------     --------------------------------------------------------------------------------
SYSTEM                             /u01/app/oracle/oradata/test/system01.dbf

TSPITR                             /u01/app/oracle/oradata/test/tspitr01.dbf

TEST                               /u01/app/oracle/oradata/test/test01.dbf

如果数据库运行在手动管理undo模式下,那么执行下面的查询就可显示包含回滚段的表空间的数据文件名

SQL> SELECT DISTINCT r.TABLESPACE_NAME AS "rbs_tbs", d.FILE_NAME AS "dbf_name"
  2  FROM DBA_ROLLBACK_SEGS r, DBA_DATA_FILES d
  3  WHERE r.TABLESPACE_NAME=d.TABLESPACE_NAME;

rbs_tbs                          dbf_name
------------------------------   --------------------------------------------------------------------------------
SYSTEM                           /u01/app/oracle/oradata/test/system01.dbf

UNDOTBS1                         /u01/app/oracle/oradata/test/undotbs01.dbf

如果数据库运行在自动管理undo模式下,那么执行下面的查询就可以显示undo表空间的数据文件名

SQL> SELECT DISTINCT u.TABLESPACE_NAME AS "undo_tbs", d.FILE_NAME AS "dbf_name"
  2  FROM DBA_UNDO_EXTENTS u, DBA_DATA_FILES d
  3  WHERE u.TABLESPACE_NAME=d.TABLESPACE_NAME;

undo_tbs                         dbf_name
------------------------------   --------------------------------------------------------------------------------
UNDOTBS1                         /u01/app/oracle/oradata/test/undotbs01.dbf

3.判断那些对象将会丢失
当对表空间执行TSPITR时,任何在恢复目标时间之后创建的对象都将会丢失。为了判断那些对象将会丢失,查询主数据库中的ts_pitr_objects_to_be_dropped视图。该视图中的owner列,显示的了将会删除的对象的所有者。name列,显示的是当执行TSPITR后丢失对象的对象名。create_time列,显示的是对象创建的时间戳。tablespace_name列,显示的是对象所存储的表空间。

SQL> SELECT OWNER, NAME, TABLESPACE_NAME,
  2  TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')
  3  FROM SYS.TS_PITR_OBJECTS_TO_BE_DROPPED
  4  WHERE TABLESPACE_NAME IN ('TSPITR','TEST')
  5  AND CREATION_TIME > TO_DATE('2015-04-07 12:00:00','YYYY-MM-DD:HH24:MI:SS')
  6  ORDER BY TABLESPACE_NAME, CREATION_TIME;

no rows selected

4.选择连接辅助实例的方法
要必须能够连接到辅助实例。可以使用Oracle Net或操作系统审计方法

5.为辅助实例创建密码文件
创建密码文件使用orapwd命令,如果不使用密码文件可以跳过这一步

6.为辅助实例创建初始化参数文件
创建一个新的初始化参数文件而不是复制一个再修改。通过对以下参数设置较低的内存来节省内存开销:
.db_cache_size
.shared_pool_size
.large_pool_size

辅助数据库可以与主数据库在相同主机上或者在不同的主机上。因为辅助数据库的数据文件名与主数据库的数据文件名相同,你必须更新辅助控制文件来指定辅助数据库的数据文件。如果辅助数据库与主数据库在同一台主机上,或者辅助数据库与主数据库在不同的主机上但使用不同的路径名,那么必须重命名控制文件,数据文件和联机重做日志文件。如果辅助数据库与主数据库在不同的主机上且使用相同的目录路径,那么只需要重命名联机重做日志文件。通过查看主数据库的联机重做日志文件名所以可以确保在创建辅助数据库时使用唯一的联机重做日志文件名。

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/redo02.log
/u01/app/oracle/oradata/test/redo03.log
/u01/app/oracle/oradata/test/redo01.log

辅助实例初始化参数
db_name:辅助数据库名。该值与主数据库相同
control_files:辅助数据库控制文件。如果辅助数据库与主数据库在相同主机上,确保控制文件名与主数据库的控制文件名不一样。
db_unique_name:就算辅助数据库的值与主数据库相同也允许启动辅助数据库。将它设置成唯一值,例如db_unique_name=aux。这个参数只有辅助数据库与主数据库在相同主机上才需要设置。
db_file_name_convert:使用模式来转换辅助数据库的数据文件名。这个参数只有在与主数据库相同的主要上还原辅助数据库时或者在不同的主机上使用不同的目录路径时才需要。
log_file_name_convert:使用模式来转换辅助数据库的联机重做日志文件名。这个参数是强制性的。
log_archive_dest_1:指定恢复所需要的归档重做日志的缺省目录。这个参数指定辅助主机上的目录。
log_archive_format:指定归档重做日志文件格式。应该使用与主数据库相同的参数值。

在相同主机上通过还原和恢复辅助数据库执行用户管理的TSPITR
下面的例子假设在主数据库test所在主机oracle11g上还原辅助数据库。在这种情况下,主数据库的所有文件都存储在目录/u01/app/oracle/oradata/test目录中,并且想还原辅助数据库到/u02/auxiliary目录中。所以需要设置db_file_name_convert和log_file_name_convert来转换文件名。下面的例子将对数据库test中的表空间tspitr,test执行按时间点恢复,其执行步骤如下:

1.先对主数据库test使用操作系统命令进行备份

[oracle@oracle11g test]$ ls -lrt
total 1450748
-rw-r----- 1 oracle oinstall  52436992 Apr  7 15:43 jy01.dbf.bak
-rw-r----- 1 oracle oinstall  52436992 Apr  8 09:39 temp01.dbf
-rw-r----- 1 oracle oinstall  10493952 Apr  9 08:57 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr  9 08:57 tspitr01.dbf
-rw-r----- 1 oracle oinstall  10493952 Apr  9 08:57 test01.dbf
-rw-r----- 1 oracle oinstall  52429312 Apr  9 08:57 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Apr  9 08:57 redo02.log
-rw-r----- 1 oracle oinstall  52436992 Apr  9 08:57 jy01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr  9 08:57 example01.dbf
-rw-r----- 1 oracle oinstall 293609472 Apr  9 16:02 sysaux01.dbf
-rw-r----- 1 oracle oinstall 178266112 Apr  9 16:12 undotbs01.dbf
-rw-r----- 1 oracle oinstall 492838912 Apr  9 16:12 system01.dbf
-rw-r----- 1 oracle oinstall  52429312 Apr  9 16:20 redo01.log
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:21 control03.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:21 control02.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:21 control01.ctl

[oracle@oracle11g test]$ cp *.dbf /u02/backup/
SQL> alter database backup controlfile to '/u02/control.ctl';

Database altered

要注意的是控制文件不能使用操作系统命令cp来创建否则会出现如下错误:

SQL> alter database mount clone database;
alter database mount clone database
*
ERROR at line 1:
ORA-01696: control file is not a clone control file


[oracle@oracle11g test]$ ls -lrt /u02/backup/
total 1450744
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:22 control01.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:22 control02.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:22 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Apr  9 16:22 example01.dbf
-rw-r----- 1 oracle oinstall  52436992 Apr  9 16:22 jy01.dbf
-rw-r----- 1 oracle oinstall  52436992 Apr  9 16:22 jy01.dbf.bak
-rw-r----- 1 oracle oinstall  52429312 Apr  9 16:22 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Apr  9 16:22 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Apr  9 16:22 redo03.log
-rw-r----- 1 oracle oinstall 293609472 Apr  9 16:23 sysaux01.dbf
-rw-r----- 1 oracle oinstall  10493952 Apr  9 16:23 test01.dbf
-rw-r----- 1 oracle oinstall  52436992 Apr  9 16:23 temp01.dbf
-rw-r----- 1 oracle oinstall 492838912 Apr  9 16:23 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr  9 16:23 tspitr01.dbf
-rw-r----- 1 oracle oinstall 178266112 Apr  9 16:24 undotbs01.dbf
-rw-r----- 1 oracle oinstall  10493952 Apr  9 16:24 users01.dbf

2.将辅助集和恢复集文件的备份还原到新目录中/u02/auxiliary

[oracle@oracle11g test]$ cp /u02/control.ctl /u02/auxiliary/control01.ctl
[oracle@oracle11g test]$ cp /u02/control.ctl /u02/auxiliary/control02.ctl
[oracle@oracle11g test]$ cp /u02/control.ctl /u02/auxiliary/control03.ctl
[oracle@oracle11g test]$ cp /u02/backup/system01.dbf /u02/auxiliary/
[oracle@oracle11g test]$ cp /u02/backup/sysaux01.dbf /u02/auxiliary/
[oracle@oracle11g test]$ cp /u02/backup/undotbs01.dbf /u02/auxiliary/
[oracle@oracle11g test]$ cp /u02/backup/tspitr01.dbf /u02/auxiliary/
[oracle@oracle11g test]$ cp /u02/backup/test01.dbf /u02/auxiliary/
[oracle@oracle11g test]$ ls -lrt /u02/auxiliary/
total 1077500
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:29 control03.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:29 control02.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:29 control01.ctl
-rw-r----- 1 oracle oinstall 492838912 Apr  9 16:30 system01.dbf
-rw-r----- 1 oracle oinstall 293609472 Apr  9 16:31 sysaux01.dbf
-rw-r----- 1 oracle oinstall 178266112 Apr  9 16:33 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr  9 16:33 tspitr01.dbf
-rw-r----- 1 oracle oinstall  10493952 Apr  9 16:34 test01.dbf

3.创建参数文件

[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'
log_archive_format='%t_%s_%r.dbf'
compatible=10.2.0.5.0

4.使用创建的参数文件 将辅助数据库启动到nomount状态

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

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Apr 9 16:46: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

5.指定clone关键字来mount辅助数据库

SQL> alter database mount clone database;

Database altered.

6.手动重命名所有辅助数据文件只有没有使用db_file_name_convert和log_file_name_convert参数时才需要执行。在这里使用了这两个参数来重命名所有的数据文件和联机重做日志文件。

7.执行以下脚本来确辅助数据库的所有数据文件名命名是否正确

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/tspitr01.dbf
/u02/auxiliary/test01.dbf
/u02/auxiliary/jy01.dbf
/u02/auxiliary/redo02.log
/u02/auxiliary/redo03.log
/u02/auxiliary/redo01.log
/u02/auxiliary/control01.ctl
/u02/auxiliary/control02.ctl
/u02/auxiliary/control03.ctl

14 rows selected.

如果不正确在步骤6中手动重命名数据文件

8.将辅助集和恢复集表空间的数据文件设置为联机状态。

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/sysaux01.dbf' online;

Database altered.

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

Database altered.

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

Database altered.

9.使用using backup controlfile选项将辅助数据库恢复到指定的恢复目标时间点。

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1718118 generated at 04/09/2015 08:57:02 needed for thread 1
ORA-00289: suggestion : /u02/1_13_876413942.dbf
ORA-00280: change 1718118 for thread 1 is in sequence #13


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/test/redo01.log
Log applied.
Media recovery complete.

10.以resetlogs选项来打开辅助数据库

SQL> alter database open resetlogs;

Database altered.

下面就是执行传输表空间的操作
11.使用管理权限连接辅助数据库

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

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Apr 9 20:12:27 2015

Copyright (c) 1982, 2010, 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

12.执行alter tablespace … read only语句将执行完按时间点恢复的表空间tspitr,test设置为只读状态

SQL> alter tablespace tspitr read only;

Tablespace altered.

SQL> alter tablespace test read only;

Tablespace altered.

13.对表空间tspitr,test执行自包含检查

SQL> exec sys.dbms_tts.transport_set_check('tspitr,test',true,true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

14.使用Oracle导出工具生成传输表空间集的元数据

[oracle@oracle11g ~]$ export ORACLE_SID=auxiliary
[oracle@oracle11g u02]$ exp \'sys/jy as sysdba\' transport_tablespace=y tablespaces=tspitr,test tts_full_check=y file=/u02/transport_tablespace.dmp log=/u02/transport_tablespace.log

Export: Release 10.2.0.5.0 - Production on Thu Apr 9 20:29:58 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 transportable tablespace metadata...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                         TSPITR
For tablespace TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                           TEST
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

下面将表空间tspitr,test附加到主数据库中
15.连接到主数据库

[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Apr 9 21:07:39 2015

Copyright (c) 1982, 2010, 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

16.从主数据库中将表空间tspitr,test删除

SQL> drop tablespace tspitr including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

17.将辅助数据库中的恢复集表空间的数据文件复制到主数据库目录/u01/app/oracle/oradata/test中

[oracle@oracle11g auxiliary]$ cp test01.dbf /u01/app/oracle/oradata/test/
[oracle@oracle11g auxiliary]$ cp tspitr01.dbf /u01/app/oracle/oradata/test/
[oracle@oracle11g auxiliary]$ ls -lrt /u01/app/oracle/oradata/test/
total 1450748
-rw-r----- 1 oracle oinstall  52436992 Apr  7 15:43 jy01.dbf.bak
-rw-r----- 1 oracle oinstall  52429312 Apr  9 08:57 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Apr  9 08:57 redo02.log
-rw-r----- 1 oracle oinstall  10493952 Apr  9 16:59 users01.dbf
-rw-r----- 1 oracle oinstall  52436992 Apr  9 16:59 jy01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr  9 16:59 example01.dbf
-rw-r----- 1 oracle oinstall  52436992 Apr  9 17:18 temp01.dbf
-rw-r----- 1 oracle oinstall 293609472 Apr  9 21:02 sysaux01.dbf
-rw-r----- 1 oracle oinstall 178266112 Apr  9 21:11 undotbs01.dbf
-rw-r----- 1 oracle oinstall 492838912 Apr  9 21:11 system01.dbf
-rw-r----- 1 oracle oinstall  52429312 Apr  9 21:11 redo01.log
-rw-r----- 1 oracle oinstall  10493952 Apr  9 21:11 test01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr  9 21:12 tspitr01.dbf
-rw-r----- 1 oracle oinstall   7389184 Apr  9 21:12 control03.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 21:12 control02.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 21:12 control01.ctl

18.将传输表空间集中的表空间test,tspitr附加到主数据库test中

[oracle@oracle11g auxiliary]$ export ORACLE_SID=test
[oracle@oracle11g auxiliary]$ imp \'sys/zzh_2046 as sysdba\' transport_tablespace=y file=/u02/transport_tablespace.dmp log=/u02/transport_tablespace_dr.log datafiles='/u01/app/oracle/oradata/test/tspitr01.dbf','/u01/app/oracle/oradata/test/test01.dbf'

Import: Release 10.2.0.5.0 - Production on Thu Apr 9 21:17:04 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 transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing TSPITR's objects into TSPITR
. . importing table                       "TSPITR"
. importing TEST's objects into TEST
. . importing table                         "TEST"
. importing SYS's objects into SYS
Import terminated successfully without warnings.


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
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/system01.dbf
/u01/app/oracle/oradata/test/undotbs01.dbf
/u01/app/oracle/oradata/test/sysaux01.dbf
/u01/app/oracle/oradata/test/users01.dbf
/u01/app/oracle/oradata/test/example01.dbf
/u01/app/oracle/oradata/test/tspitr01.dbf
/u01/app/oracle/oradata/test/test01.dbf
/u01/app/oracle/oradata/test/jy01.dbf
/u01/app/oracle/oradata/test/redo02.log
/u01/app/oracle/oradata/test/redo03.log
/u01/app/oracle/oradata/test/redo01.log

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/control01.ctl
/u01/app/oracle/oradata/test/control02.ctl
/u01/app/oracle/oradata/test/control03.ctl

14 rows selected.

SQL> select count(*) from tspitr.tspitr;

  COUNT(*)
----------
     50707

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

  COUNT(*)
----------
     50683

19.将辅助数据库中的表空间tspitr,test设置为读写模式

SQL> alter tablespace tspitr read write;

Tablespace altered.

SQL> alter tablespace test read write;

Tablespace altered.

20.使用操作系统工具对恢复的表空间进行备份或者RMAN对整个主数据库进行备份这里不再赘述

在不同的主机使用不同的目录路径还原辅助数据执行用户管理的TSPITR
下面的例子假设在主机jingyong1上创建辅助数据库aux,主数据库test在主机oracle11g上,这里仍然使用db_file_name_covert和log_file_name_convert参数进行数据文件和联机重做日志文件的转换.
1.先对主数据库test使用操作系统命令进行备份

[oracle@oracle11g test]$ ls -lrt
total 1450748
-rw-r----- 1 oracle oinstall  52436992 Apr  7 15:43 jy01.dbf.bak
-rw-r----- 1 oracle oinstall  52436992 Apr  8 09:39 temp01.dbf
-rw-r----- 1 oracle oinstall  10493952 Apr  9 08:57 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr  9 08:57 tspitr01.dbf
-rw-r----- 1 oracle oinstall  10493952 Apr  9 08:57 test01.dbf
-rw-r----- 1 oracle oinstall  52429312 Apr  9 08:57 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Apr  9 08:57 redo02.log
-rw-r----- 1 oracle oinstall  52436992 Apr  9 08:57 jy01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr  9 08:57 example01.dbf
-rw-r----- 1 oracle oinstall 293609472 Apr  9 16:02 sysaux01.dbf
-rw-r----- 1 oracle oinstall 178266112 Apr  9 16:12 undotbs01.dbf
-rw-r----- 1 oracle oinstall 492838912 Apr  9 16:12 system01.dbf
-rw-r----- 1 oracle oinstall  52429312 Apr  9 16:20 redo01.log
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:21 control03.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:21 control02.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 16:21 control01.ctl

[oracle@oracle11g test]$ cp *.dbf /u02/backup/
SQL> alter database backup controlfile to '/u02/control.ctl';

Database altered

要注意的是控制文件不能使用操作系统命令cp来创建否则会出现如下错误:

SQL> alter database mount clone database;
alter database mount clone database
*
ERROR at line 1:
ORA-01696: control file is not a clone control file


[oracle@oracle11g test]$ ls -lrt /u02/backup/
-rw-r----- 1 oracle oinstall 104865792 Apr  9 16:22 example01.dbf
-rw-r----- 1 oracle oinstall  52436992 Apr  9 16:22 jy01.dbf
-rw-r----- 1 oracle oinstall  52436992 Apr  9 16:22 jy01.dbf.bak
-rw-r----- 1 oracle oinstall  52429312 Apr  9 16:22 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Apr  9 16:22 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Apr  9 16:22 redo03.log
-rw-r----- 1 oracle oinstall 293609472 Apr  9 16:23 sysaux01.dbf
-rw-r----- 1 oracle oinstall  10493952 Apr  9 16:23 test01.dbf
-rw-r----- 1 oracle oinstall  52436992 Apr  9 16:23 temp01.dbf
-rw-r----- 1 oracle oinstall 492838912 Apr  9 16:23 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr  9 16:23 tspitr01.dbf
-rw-r----- 1 oracle oinstall 178266112 Apr  9 16:24 undotbs01.dbf
-rw-r----- 1 oracle oinstall  10493952 Apr  9 16:24 users01.dbf

2.将辅助集和恢复集文件的备份和需要的归档重做日志和联机重做日志文件还原到新目录/u02/aux,/u02/archive中

[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/backup/*.dbf /u02/aux
oracle@192.168.56.2's password:
sysaux01.dbf                                                                                                                         100%  280MB   7.4MB/s   00:38
system01.dbf                                                                                                                         100%  470MB   7.2MB/s   01:05
test01.dbf                                                                                                                           100%   10MB   5.0MB/s   00:02
tspitr01.dbf                                                                                                                         100%  100MB   7.7MB/s   00:13
undotbs01.dbf                                                                                                                        100%  170MB   5.0MB/s   00:34
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/control.ctl /u02/aux
oracle@192.168.56.2's password:
control.ctl                                                                                                                          100% 7216KB   7.1MB/s   00:00
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/1_11_876413942.dbf /u02/archive
oracle@192.168.56.2's password:
1_11_876413942.dbf                                                                                                                   100% 5751KB   5.6MB/s   00:01
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/1_12_876413942.dbf /u02/archive
oracle@192.168.56.2's password:
1_12_876413942.dbf                                                                                                                   100%   15MB  14.9MB/s   00:01
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u01/app/oracle/oradata/test/*.log /u02/aux
oracle@192.168.56.2's password:
redo01.log                                                                                                                           100%   50MB   8.3MB/s   00:06
redo02.log                                                                                                                           100%   50MB  12.5MB/s   00:04
redo03.log                                                                                                                           100%   50MB  10.0MB/s   00:05


[oracle@jingyong1 aux]$ ls -lrt
total 1216824
-rw-r----- 1 oracle oinstall 293609472 Apr  9 20:43 sysaux01.dbf
-rw-r----- 1 oracle oinstall 492838912 Apr  9 20:44 system01.dbf
-rw-r----- 1 oracle oinstall  10493952 Apr  9 20:44 test01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr  9 20:44 tspitr01.dbf
-rw-r----- 1 oracle oinstall 178266112 Apr  9 20:45 undotbs01.dbf
-rw-r----- 1 oracle oinstall   7389184 Apr  9 20:46 control.ctl
-rw-r----- 1 oracle oinstall  52429312 Apr  9 20:47 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Apr  9 20:47 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Apr  9 20:47 redo03.log
[oracle@jingyong1 aux]$ ls -lrt /u02/archive/
total 21076
-rw-r----- 1 oracle oinstall  5888512 Apr  9 20:46 1_11_876413942.dbf
-rw-r----- 1 oracle oinstall 15657472 Apr  9 20:47 1_12_876413942.dbf

3.创建参数文件

[oracle@jingyong1 aux]$ vi initaux.ora
db_name=test
db_unique_name=aux
sga_max_size=160M
sga_target=160M
pga_aggregate_target=16M
db_file_name_convert=('/u01/app/oracle/oradata/test/','/u02/aux/')
log_file_name_convert=('/u01/app/oracle/oradata/test/','/u02/aux/')
control_files='/u02/aux/control.ctl'
log_archive_dest_1='location=/u02/archive'
log_archive_format='%t_%s_%r.dbf'
compatible=10.2.0.5.0

4.使用创建的参数文件 将辅助数据库启动到nomount状态

[oracle@jingyong1 ~]$ export ORACLE_SID=aux
[oracle@jingyong1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Apr 9 20:56:16 2015

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

Connected to an idle instance.

SQL> startup nomount pfile='/u02/aux/initaux.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

5.指定clone关键字来mount辅助数据库

SQL> alter database mount clone database;

Database altered.

6.手动重命名所有辅助数据文件只有没有使用db_file_name_convert和log_file_name_convert参数时才需要执行。在这里使用了这两个参数来重命名所有的数据文件和联机重做日志文件。

7.执行以下脚本来确辅助数据库的所有数据文件名命名是否正确

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/aux/system01.dbf
/u02/aux/undotbs01.dbf
/u02/aux/sysaux01.dbf
/u02/aux/users01.dbf
/u02/aux/example01.dbf
/u02/aux/tspitr01.dbf
/u02/aux/test01.dbf
/u02/aux/jy01.dbf
/u02/aux/redo02.log
/u02/aux/redo03.log
/u02/aux/redo01.log
/u02/aux/control.ctl

12 rows selected.

如果不正确在步骤6中手动重命名数据文件

8.将辅助集和恢复集表空间的数据文件设置为联机状态。

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

Database altered.

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

Database altered.

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

Database altered.

SQL> alter database datafile '/u02/aux/tspitr01.dbf' online;

Database altered.

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

Database altered.

9.使用using backup controlfile选项将辅助数据库恢复到指定的恢复目标时间点

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1718118 generated at 04/09/2015 08:57:02 needed for thread 1
ORA-00289: suggestion : /u02/archive/1_13_876413942.dbf
ORA-00280: change 1718118 for thread 1 is in sequence #13


Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/aux/redo01.log
Log applied.
Media recovery complete.

10.以resetlogs选项来打开辅助数据库

SQL> alter database open resetlogs;

Database altered.

下面就是执行传输表空间的操作
11.使用管理权限连接辅助数据库

[oracle@jingyong1 ~]$ export ORACLE_SID=aux
[oracle@jingyong1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Apr 9 21:03:23 2015

Copyright (c) 1982, 2010, 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

12.执行alter tablespace … read only语句将执行完按时间点恢复的表空间tspitr,test设置为只读状态

SQL> alter tablespace tspitr read only;

Tablespace altered.

SQL> alter tablespace test read only;

Tablespace altered.

13.对表空间tspitr,test执行自包含检查

SQL> exec sys.dbms_tts.transport_set_check('tspitr,test',true,true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

14.使用Oracle导出工具生成传输表空间集的元数据

[oracle@jingyong1 u02]$ export ORACLE_SID=aux
[oracle@jingyong1 u02]$ exp \'sys/jy as sysdba\' transport_tablespace=y tablespaces=tspitr,test tts_full_check=y file=/u02/tts.dmp log=/u02/tts.log

Export: Release 10.2.0.5.0 - Production on Thu Apr 9 21:05:53 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 transportable tablespace metadata...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                         TSPITR
For tablespace TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                           TEST
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

下面将表空间tspitr,test附加到主数据库中
15.连接到主数据库

[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Apr 9 21:22:32 2015

Copyright (c) 1982, 2010, 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

16.从主数据库中将表空间tspitr,test删除

SQL> drop tablespace tspitr including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

17.将辅助数据库中的恢复集表空间的数据文件复制到主数据库目录/u01/app/oracle/oradata/test中

[oracle@oracle11g test]$ scp -r oracle@192.168.56.11:/u02/tts.dmp /u02/
oracle@192.168.56.11's password:
tts.dmp                                                                                                                              100%   24KB  24.0KB/s   00:00
[oracle@oracle11g test]$ scp -r oracle@192.168.56.11:/u02/aux/tspitr01.dbf /u01/app/oracle/oradata/test/
oracle@192.168.56.11's password:
tspitr01.dbf                                                                                                                         100%  100MB   6.3MB/s   00:16
[oracle@oracle11g test]$ scp -r oracle@192.168.56.11:/u02/aux/test01.dbf /u01/app/oracle/oradata/test/
oracle@192.168.56.11's password:
test01.dbf                                                                                                                           100%   10MB   5.0MB/s   00:02
[oracle@oracle11g test]$ ls -lrt
total 1450748
-rw-r----- 1 oracle oinstall  52436992 Apr  7 15:43 jy01.dbf.bak
-rw-r----- 1 oracle oinstall  52429312 Apr  9 08:57 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Apr  9 08:57 redo02.log
-rw-r----- 1 oracle oinstall  10493952 Apr  9 16:59 users01.dbf
-rw-r----- 1 oracle oinstall  52436992 Apr  9 16:59 jy01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr  9 16:59 example01.dbf
-rw-r----- 1 oracle oinstall  52436992 Apr  9 17:18 temp01.dbf
-rw-r----- 1 oracle oinstall 293609472 Apr  9 21:19 sysaux01.dbf
-rw-r----- 1 oracle oinstall 492838912 Apr  9 21:24 system01.dbf
-rw-r----- 1 oracle oinstall 178266112 Apr  9 21:25 undotbs01.dbf
-rw-r----- 1 oracle oinstall  52429312 Apr  9 21:25 redo01.log
-rw-r----- 1 oracle oinstall 104865792 Apr  9 21:25 tspitr01.dbf
-rw-r----- 1 oracle oinstall  10493952 Apr  9 21:26 test01.dbf
-rw-r----- 1 oracle oinstall   7389184 Apr  9 21:26 control03.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 21:26 control02.ctl
-rw-r----- 1 oracle oinstall   7389184 Apr  9 21:26 control01.ctl
[oracle@oracle11g test]$ ls -lrt /u02/
-rw-r--r-- 1 oracle oinstall      24576 Apr  9 21:25 tts.dmp

18.将传输表空间集中的表空间test,tspitr附加到主数据库test中

[oracle@oracle11g auxiliary]$ export ORACLE_SID=test
[oracle@oracle11g auxiliary]$ imp \'sys/zzh_2046 as sysdba\' transport_tablespace=y file=/u02/tts.dmp log=/u02/tts_dr.log datafiles='/u01/app/oracle/oradata/test/tspitr01.dbf','/u01/app/oracle/oradata/test/test01.dbf'

Import: Release 10.2.0.5.0 - Production on Thu Apr 9 21:27:47 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 transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing TSPITR's objects into TSPITR
. . importing table                       "TSPITR"
. importing TEST's objects into TEST
. . importing table                         "TEST"
. importing SYS's objects into SYS
Import terminated successfully without warnings.


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
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/system01.dbf
/u01/app/oracle/oradata/test/undotbs01.dbf
/u01/app/oracle/oradata/test/sysaux01.dbf
/u01/app/oracle/oradata/test/users01.dbf
/u01/app/oracle/oradata/test/example01.dbf
/u01/app/oracle/oradata/test/tspitr01.dbf
/u01/app/oracle/oradata/test/test01.dbf
/u01/app/oracle/oradata/test/jy01.dbf
/u01/app/oracle/oradata/test/redo02.log
/u01/app/oracle/oradata/test/redo03.log
/u01/app/oracle/oradata/test/redo01.log
/u01/app/oracle/oradata/test/control01.ctl
/u01/app/oracle/oradata/test/control02.ctl
/u01/app/oracle/oradata/test/control03.ctl

14 rows selected.

SQL> select count(*) from tspitr.tspitr;

  COUNT(*)
----------
     50707

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

  COUNT(*)
----------
     50683

19.将辅助数据库中的表空间tspitr,test设置为读写模式

SQL> alter tablespace tspitr read write;

Tablespace altered.

SQL> alter tablespace test read write;

Tablespace altered.

20.使用操作系统工具对恢复的表空间进行备份或者RMAN对整个主数据库进行备份这里不再赘述

利用RMAN在文件系统与ASM之间迁移数据库

在Oracle10g中在操作系统与ASM之间不能使用复制命令(11g有相关复制命令)比如cp操作来复制文件,为了使用ASM来管理已经存在的数据库必须将数据库迁移到ASM中。可以通过RMAN来完成这项操作。假设在文件系统和ASM磁盘组中有足够的空间来存储整个数据库可以将数据库从文件系统中直接迁移到ASM中。如果在ASM和文件系统同时没有足够的空间来存储整个数据库,可以将数据库备份到磁带,使用旧的磁盘来创建ASM磁盘组并使用磁带备份将数据库还原到ASM中。

如果文件系统和ASM同时有足够的空间来存储整个数据库,可以不使用磁带过渡直接将数据库迁移到ASM中。在这个例子中目标ASM磁盘组为+DISK1。在迁移的过程中所有的闪回日志将会被丢弃,因此,数据库中的任何受保护还原点都将变为无效。在执行迁移之前需要将其删除。

将数据库从文件系统迁移到ASM的操作步骤如下:
1.记录数据库迁移之前的数据文件,控制文件,联机重做日志文件的文件名

SQL> select name,status from v$datafile;

NAME                                                                             STATUS
-------------------------------------------------------------------------------- -------
/u01/app/oracle/oradata/test/system01.dbf                                        SYSTEM
/u01/app/oracle/oradata/test/undotbs01.dbf                                       ONLINE
/u01/app/oracle/oradata/test/sysaux01.dbf                                        ONLINE
/u01/app/oracle/oradata/test/users01.dbf                                         ONLINE
/u01/app/oracle/oradata/test/example01.dbf                                       ONLINE
/u01/app/oracle/oradata/test/tspitr01.dbf                                        ONLINE
/u01/app/oracle/oradata/test/test01.dbf                                          ONLINE

7 rows selected

SQL> select member,status from v$logfile;

MEMBER                                                                           STATUS
-------------------------------------------------------------------------------- -------
/u01/app/oracle/oradata/test/redo03.log
/u01/app/oracle/oradata/test/redo02.log
/u01/app/oracle/oradata/test/redo01.log

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/test/control01.ctl,
                                                 /u01/app/oracle/oradata/test/control02.ctl,
                                                 /u01/app/oracle/oradata/test/control03.ctl

2.将数据库文件作为副本备份到ASM磁盘组

[oracle@oracle11g ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
[oracle@oracle11g ~]$ rman target sys/zzh_2046@test  catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Apr 1 16:06:19 2015

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

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database

RMAN> backup as copy incremental level 0 database format '+DISK1' tag 'DB_ASM_MIGRATION';

Starting backup at 2015-04-01 16:06:27
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf
output filename=+DISK1/test/datafile/system.266.875894803 tag=DB_ASM_MIGRATION recid=17 stamp=875894833
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf
output filename=+DISK1/test/datafile/sysaux.267.875894845 tag=DB_ASM_MIGRATION recid=18 stamp=875894865
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf
output filename=+DISK1/test/datafile/example.265.875894871 tag=DB_ASM_MIGRATION recid=19 stamp=875894883
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
output filename=+DISK1/test/datafile/tspitr.264.875894885 tag=DB_ASM_MIGRATION recid=20 stamp=875894892
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf
output filename=+DISK1/test/datafile/undotbs1.263.875894899 tag=DB_ASM_MIGRATION recid=21 stamp=875894903
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf
output filename=+DISK1/test/datafile/users.262.875894907 tag=DB_ASM_MIGRATION recid=22 stamp=875894907
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u01/app/oracle/oradata/test/test01.dbf
output filename=+DISK1/test/datafile/test.261.875894909 tag=DB_ASM_MIGRATION recid=23 stamp=875894910
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2015-04-01 16:08:30

Starting Control File and SPFILE Autobackup at 2015-04-01 16:08:30
piece handle=/u02/c-2168949517-20150401-08 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 16:08:35


ASMCMD> ls -lrt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   APR 01 16:00:00  Y    EXAMPLE.265.875894871
DATAFILE  UNPROT  COARSE   APR 01 16:00:00  Y    SYSAUX.267.875894845
DATAFILE  UNPROT  COARSE   APR 01 16:00:00  Y    SYSTEM.266.875894803
DATAFILE  UNPROT  COARSE   APR 01 16:00:00  Y    TEST.261.875894909
DATAFILE  UNPROT  COARSE   APR 01 16:00:00  Y    TSPITR.264.875894885
DATAFILE  UNPROT  COARSE   APR 01 16:00:00  Y    UNDOTBS1.263.875894899
DATAFILE  UNPROT  COARSE   APR 01 16:00:00  Y    USERS.262.875894907

为了确保数据库能执行一致性恢复,归档当前联机重做日

RMAN> sql 'alter system archive log current';

sql statement: alter system archive log current

如果数据库很大在执行上面的备份时可能会要很长时间。如果在执行备份时有大量的数据库活动,可以对数据库生成一个增量备份在迁移之前将0级备份之后的改变刷新到0级备份所创建的副本中。

RMAN> backup incremental level 1 for recover of copy with tag 'DB_ASM_MIGRATION' database;

Starting backup at 2015-04-01 16:19:52
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/test/test01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-04-01 16:19:53
channel ORA_DISK_1: finished piece 1 at 2015-04-01 16:20:38
piece handle=/u02/ora_test875895593_1301 tag=DB_ASM_MIGRATION comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 2015-04-01 16:20:39

Starting Control File and SPFILE Autobackup at 2015-04-01 16:20:39
piece handle=/u02/c-2168949517-20150401-09 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 16:20:42

RMAN> recover copy of database with tag 'DB_ASM_MIGRATION';

Starting recover at 2015-04-01 16:21:09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy fno=00001 name=+DISK1/test/datafile/system.266.875894803
recovering datafile copy fno=00002 name=+DISK1/test/datafile/undotbs1.263.875894899
recovering datafile copy fno=00003 name=+DISK1/test/datafile/sysaux.267.875894845
recovering datafile copy fno=00004 name=+DISK1/test/datafile/users.262.875894907
recovering datafile copy fno=00005 name=+DISK1/test/datafile/example.265.875894871
recovering datafile copy fno=00006 name=+DISK1/test/datafile/tspitr.264.875894885
recovering datafile copy fno=00007 name=+DISK1/test/datafile/test.261.875894909
channel ORA_DISK_1: reading from backup piece /u02/ora_test875895593_1301
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875895593_1301 tag=DB_ASM_MIGRATION
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished recover at 2015-04-01 16:21:19

Starting Control File and SPFILE Autobackup at 2015-04-01 16:21:19
piece handle=/u02/c-2168949517-20150401-0a comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 16:21:23

3.在ASM磁盘组中创建一个SPFILE文件副本。将要迁移的数据库SPFILE文件存储为+DISK1/spfile如果数据库是使用的SPFILE启动,就执行以下命令:

RMAN> run
2> {
3> backup as backupset spfile;
4> restore spfile to '+DISK1/spfile';
5> }

Starting backup at 2015-04-01 16:24:46
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2015-04-01 16:24:48
channel ORA_DISK_1: finished piece 1 at 2015-04-01 16:24:49
piece handle=/u02/ora_test875895887_1331 tag=TAG20150401T162446 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2015-04-01 16:24:49

Starting Control File and SPFILE Autobackup at 2015-04-01 16:24:49
piece handle=/u02/c-2168949517-20150401-0b comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 16:24:51

Starting restore at 2015-04-01 16:24:52
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=+DISK1/spfile
channel ORA_DISK_1: reading from backup piece /u02/c-2168949517-20150401-0b
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/c-2168949517-20150401-0b tag=TAG20150401T162450
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 2015-04-01 16:24:56


ASMCMD> ls -lrt
Type           Redund  Striped  Time             Sys  Name
                                                 Y    DB_UNKNOWN/
                                                 Y    TEST/
                                                 N    spfile => +DISK1/DB_UNKNOWN/PARAMETERFILE/SPFILE.260.875895895

如果数据库是使用PFILE启动,可以在SQL*Plus中执行create spfile命令来创建SPFILE

SQL>create spfile='+DISK1/spfile' from pfile='$ORACLE_HOME/dbs/inittest.ora'

File created.

4.如果被迁移的数据库是备库,需要停止管理恢复模式

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

执行一致性关闭

RMAN> SHUTDOWN IMMEDIATE;


5.在目录/u02/backup中创建一个 pfile参数文件来引用新的SPFILE文件,并重新启动实例到nomount状态

[oracle@oracle11g backup]$ vi inittest_temp.ora
spfile='+DISK1/spfile'

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

SQL> startup nomount pfile='/u02/backup/inittest_temp.ora'
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size             109053160 bytes
Database Buffers           54525952 bytes
Redo Buffers                2920448 bytes

6.为了下一步将控制文件迁移到ASM中,修改control_files参数:

SQL> alter system set control_files='+DISK1/control01.ctl' scope=spfile;

System altered.

7.通过设置db_recovery_file_dest和db_recovery_file_dest_size来指定闪回区,这里闪回区大小设置为2G(因为这里只是进行迁移测试实验)

SQL> alter system set db_recovery_file_dest_size=2G;

System altered.

SQL> alter system set db_recovery_file_dest='+DISK1';

System altered.

8.将实例重新启动到nomount状态,使用RMAN在ASM中创建新的控制文件(使用原来控制文件来创建),切换数据文件,并执执恢复

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount pfile='/u02/backup/inittest_temp.ora'
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size             109053160 bytes
Database Buffers           54525952 bytes
Redo Buffers                2920448 bytes

RMAN> restore controlfile from '/u01/app/oracle/oradata/test/control01.ctl';

Starting restore at 2015-04-01 17:11:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DISK1/control01.ctl
Finished restore at 2015-04-01 17:11:28


RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DISK1/test/datafile/system.266.875894803"
datafile 2 switched to datafile copy "+DISK1/test/datafile/undotbs1.263.875894899"
datafile 3 switched to datafile copy "+DISK1/test/datafile/sysaux.267.875894845"
datafile 4 switched to datafile copy "+DISK1/test/datafile/users.262.875894907"
datafile 5 switched to datafile copy "+DISK1/test/datafile/example.265.875894871"
datafile 6 switched to datafile copy "+DISK1/test/datafile/tspitr.264.875894885"
datafile 7 switched to datafile copy "+DISK1/test/datafile/test.261.875894909"
starting full resync of recovery catalog
full resync complete

RMAN> recover database;

Starting recover at 2015-04-01 17:12:29
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 2015-04-01 17:12:33

9.将临时文件迁移到ASM,必须为每个临时文件执行set newname命令,然后执行switch命令

RMAN> run
2> {
3> set newname for tempfile '/u01/app/oracle/oradata/test/temp01.dbf' to '+DISK1';
4>switch tempfile all;
5> }

executing command: SET NEWNAME

renamed temporary file 1 to +DISK1 in control file
starting full resync of recovery catalog
full resync complete

10.禁用闪回数据库并重新启动闪回数据库让闪回日志创建在ASM中

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

11.改变跟踪文件不会被迁移。只能禁用改变跟踪再重新启用改变跟踪,并将改变跟踪文件存储在ASM中

SQL> alter database disable block change tracking;

Database altered.

SQL> alter database enable block change tracking using file '+DISK1';

Database altered.

12.如果迁移的是主库,打开数据库

SQL> alter database open;

Database altered.

如果是备库,恢复管理恢复模式

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

13.对于主库,要迁移联机重做日志文件是通过增加新的重做日志文件并存储在ASM中并删除旧的重做日志文件。对于备库,就是删除旧的备重做日志并增加新的备重做日志并存储在ASM中


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS            FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ----------------  ------------- ------------
         1          1         97   52428800          1 NO  CURRENT            1288360      01-APR-15
         2          1         95   52428800          1 YES INACTIVE           1226685      30-MAR-15
         3          1         96   52428800          1 YES INACTIVE           1268619      31-MAR-15

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 '+DISK1/redo02.log' size 50M;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 '+DISK1/redo03.log' size 50M;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance test (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test/redo01.log'


SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '+DISK1/redo01.log' size 50M;

Database altered.

将数据库从ASM中迁移到文件系统
1.记录数据库迁移之前的数据文件,控制文件,联机重做日志文件的文件名

SQL> select member,status from v$logfile;

MEMBER                                                                           STATUS
-------------------------------------------------------------------------------- -------
+DISK1/redo03.log
+DISK1/redo02.log
+DISK1/redo01.log

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DISK1/control01.ctl

SQL> select name,status from v$datafile;

NAME                                                                             STATUS
-------------------------------------------------------------------------------- -------
+DISK1/test/datafile/system.266.875894803                                        SYSTEM
+DISK1/test/datafile/undotbs1.263.875894899                                      ONLINE
+DISK1/test/datafile/sysaux.267.875894845                                        ONLINE
+DISK1/test/datafile/users.262.875894907                                         ONLINE
+DISK1/test/datafile/example.265.875894871                                       ONLINE
+DISK1/test/datafile/tspitr.264.875894885                                        ONLINE
+DISK1/test/datafile/test.261.875894909                                          ONLINE

7 rows selected

2.将数据库文件作为副本备份到ASM磁盘组

RMAN> backup as copy incremental level 0 database format '/u02/asm_backup/%U' tag 'NO_ASM_MIGRATION';

Starting backup at 2015-04-01 20:47:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DISK1/test/datafile/system.266.875894803
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSTEM_FNO-1_4rq3amf5 tag=NO_ASM_MIGRATION recid=46 stamp=875911696
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+DISK1/test/datafile/sysaux.267.875894845
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSAUX_FNO-3_4sq3amgj tag=NO_ASM_MIGRATION recid=47 stamp=875911724
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+DISK1/test/datafile/example.265.875894871
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-EXAMPLE_FNO-5_4tq3amhm tag=NO_ASM_MIGRATION recid=48 stamp=875911742
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=+DISK1/test/datafile/tspitr.264.875894885
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_4uq3ami5 tag=NO_ASM_MIGRATION recid=49 stamp=875911756
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+DISK1/test/datafile/undotbs1.263.875894899
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-UNDOTBS1_FNO-2_4vq3amik tag=NO_ASM_MIGRATION recid=50 stamp=875911766
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DISK1/test/datafile/users.262.875894907
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-USERS_FNO-4_50q3amin tag=NO_ASM_MIGRATION recid=51 stamp=875911768
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=+DISK1/test/datafile/test.261.875894909
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-TEST_FNO-7_51q3amio tag=NO_ASM_MIGRATION recid=52 stamp=875911769
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2015-04-01 20:49:29

Starting Control File and SPFILE Autobackup at 2015-04-01 20:49:30
piece handle=/u02/c-2168949517-20150401-16 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 20:49:34


[oracle@oracle11g asm_backup]$ ls -lrt
total 1014840
-rw-r----- 1 oracle oinstall 482353152 Apr  1 20:48 data_D-TEST_I-2168949517_TS-SYSTEM_FNO-1_4rq3amf5
-rw-r----- 1 oracle oinstall 293609472 Apr  1 20:48 data_D-TEST_I-2168949517_TS-SYSAUX_FNO-3_4sq3amgj
-rw-r----- 1 oracle oinstall 104865792 Apr  1 20:49 data_D-TEST_I-2168949517_TS-EXAMPLE_FNO-5_4tq3amhm
-rw-r----- 1 oracle oinstall 104865792 Apr  1 20:49 data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_4uq3ami5
-rw-r----- 1 oracle oinstall  31465472 Apr  1 20:49 data_D-TEST_I-2168949517_TS-UNDOTBS1_FNO-2_4vq3amik
-rw-r----- 1 oracle oinstall  10493952 Apr  1 20:49 data_D-TEST_I-2168949517_TS-USERS_FNO-4_50q3amin
-rw-r----- 1 oracle oinstall  10493952 Apr  1 20:49 data_D-TEST_I-2168949517_TS-TEST_FNO-7_51q3amio

为了确保数据库能执行一致性恢复,归档当前联机重做日

RMAN> sql 'alter system archive log current';

sql statement: alter system archive log current

如果数据库很大在执行上面的备份时可能会要很长时间。如果在执行备份时有大量的数据库活动,可以对数据库生成一个增量备份在迁移之前将0级备份之后的改变刷新到0级备份所创建的副本中。

RMAN> backup incremental level 1 for recover of copy with tag 'NO_ASM_MIGRATION' database;

Starting backup at 2015-04-01 20:51:43
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DISK1/test/datafile/system.266.875894803
input datafile fno=00003 name=+DISK1/test/datafile/sysaux.267.875894845
input datafile fno=00005 name=+DISK1/test/datafile/example.265.875894871
input datafile fno=00006 name=+DISK1/test/datafile/tspitr.264.875894885
input datafile fno=00002 name=+DISK1/test/datafile/undotbs1.263.875894899
input datafile fno=00004 name=+DISK1/test/datafile/users.262.875894907
input datafile fno=00007 name=+DISK1/test/datafile/test.261.875894909
channel ORA_DISK_1: starting piece 1 at 2015-04-01 20:51:44
channel ORA_DISK_1: finished piece 1 at 2015-04-01 20:52:19
piece handle=/u02/ora_test875911904_1631 tag=NO_ASM_MIGRATION comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 2015-04-01 20:52:19

Starting Control File and SPFILE Autobackup at 2015-04-01 20:52:19
piece handle=/u02/c-2168949517-20150401-17 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 20:52:23


RMAN> recover copy of database with tag 'NO_ASM_MIGRATION';

Starting recover at 2015-04-01 20:52:57
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy fno=00001 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSTEM_FNO-1_4rq3amf5
recovering datafile copy fno=00002 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-UNDOTBS1_FNO-2_4vq3amik
recovering datafile copy fno=00003 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSAUX_FNO-3_4sq3amgj
recovering datafile copy fno=00004 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-USERS_FNO-4_50q3amin
recovering datafile copy fno=00005 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-EXAMPLE_FNO-5_4tq3amhm
recovering datafile copy fno=00006 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_4uq3ami5
recovering datafile copy fno=00007 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-TEST_FNO-7_51q3amio
channel ORA_DISK_1: reading from backup piece /u02/ora_test875911904_1631
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875911904_1631 tag=NO_ASM_MIGRATION
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 2015-04-01 20:53:00

Starting Control File and SPFILE Autobackup at 2015-04-01 20:53:00
piece handle=/u02/c-2168949517-20150401-18 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 20:53:04

3.在/u02/asm_backup目录中创建一个SPFILE文件副本。将要迁移的数据库SPFILE文件存储为
/u02/asm_backup/spfiletest.ora如果数据库是使用的SPFILE启动,就执行以下命令:

RMAN> run
2> {
3> backup as backupset spfile;
4> restore spfile to '/u02/asm_backup/spfiletest.ora';
5> }

Starting backup at 2015-04-01 20:55:31
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2015-04-01 20:55:32
channel ORA_DISK_1: finished piece 1 at 2015-04-01 20:55:33
piece handle=/u02/ora_test875912132_1661 tag=TAG20150401T205531 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-04-01 20:55:33

Starting Control File and SPFILE Autobackup at 2015-04-01 20:55:33
piece handle=/u02/c-2168949517-20150401-19 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 20:55:36

Starting restore at 2015-04-01 20:55:38
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=/u02/asm_backup/spfiletest.ora
channel ORA_DISK_1: reading from backup piece /u02/c-2168949517-20150401-19
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/c-2168949517-20150401-19 tag=TAG20150401T205533
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2015-04-01 20:55:39

[oracle@oracle11g asm_backup]$ ls -lrt
total 1014844
-rw-r----- 1 oracle oinstall  10493952 Apr  1 20:52 data_D-TEST_I-2168949517_TS-USERS_FNO-4_50q3amin
-rw-r----- 1 oracle oinstall  31465472 Apr  1 20:52 data_D-TEST_I-2168949517_TS-UNDOTBS1_FNO-2_4vq3amik
-rw-r----- 1 oracle oinstall 104865792 Apr  1 20:52 data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_4uq3ami5
-rw-r----- 1 oracle oinstall  10493952 Apr  1 20:52 data_D-TEST_I-2168949517_TS-TEST_FNO-7_51q3amio
-rw-r----- 1 oracle oinstall 482353152 Apr  1 20:52 data_D-TEST_I-2168949517_TS-SYSTEM_FNO-1_4rq3amf5
-rw-r----- 1 oracle oinstall 293609472 Apr  1 20:52 data_D-TEST_I-2168949517_TS-SYSAUX_FNO-3_4sq3amgj
-rw-r----- 1 oracle oinstall 104865792 Apr  1 20:52 data_D-TEST_I-2168949517_TS-EXAMPLE_FNO-5_4tq3amhm
-rw-r----- 1 oracle oinstall      2560 Apr  1 20:55 spfiletest.ora

如果数据库是使用PFILE启动,可以在SQL*Plus中执行create spfile命令来创建SPFILE

SQL>create spfile='/u02/asm_backup/spfiletest.ora' from pfile='+DISK1/inittest.ora'

File created.

4.如果被迁移的数据库是备库,需要停止管理恢复模式

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

执行一致性关闭

RMAN> SHUTDOWN IMMEDIATE;

5.在目录/u02/asm_backup中创建一个 pfile参数文件来引用新的SPFILE文件,并重新启动实例到nomount状态。

[oracle@oracle11g asm_backup]$ vi inittest_temp.ora
spfile='/u02/asm_backup/spfiletest.ora'


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



SQL> startup nomount pfile='/u02/asm_backup/inittest_temp.ora'
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size             109053160 bytes
Database Buffers           54525952 bytes
Redo Buffers                2920448 bytes

6.为了下一步将控制文件迁移到文件系统中,修改control_files参数:

SQL> alter system set control_files='/u02/asm_backup/control01.ctl' scope=spfile;

System altered.

7.通过设置db_recovery_file_dest和db_recovery_file_dest_size来指定闪回区,这里闪回区大小设置为2G(因为这里只是进行迁移测试实验)

SQL> alter system set db_recovery_file_dest_size=2G;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/oradata/flash_recovery_area' scope=spfile;

System altered.

8.将实例重新启动到nomount状态,使用RMAN在文件系统中创建新的控制文件(使用原来控制文件来创建),切换数据文件,并执执恢复

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SSQL> startup nomount pfile='/u02/asm_backup/inittest_temp.ora'
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size             109053160 bytes
Database Buffers           54525952 bytes
Redo Buffers                2920448 bytes

RMAN> restore controlfile from '+DISK1/control01.ctl';

Starting restore at 2015-04-01 21:17:54
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=/u02/asm_backup/control01.ctl
Finished restore at 2015-04-01 21:18:04




RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSTEM_FNO-1_4rq3amf5"
datafile 2 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-UNDOTBS1_FNO-2_4vq3amik"
datafile 3 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSAUX_FNO-3_4sq3amgj"
datafile 4 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-USERS_FNO-4_50q3amin"
datafile 5 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-EXAMPLE_FNO-5_4tq3amhm"
datafile 6 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_4uq3ami5"
datafile 7 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-TEST_FNO-7_51q3amio"
starting full resync of recovery catalog
full resync complete

RMAN> recover database;

Starting recover at 2015-04-01 21:18:38
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 2015-04-01 21:18:41

8.将临时文件迁移到ASM,必须为每个临时文件执行set newname命令,然后执行switch命令

RMAN> run
2> {
3> set newname for tempfile '+DISK1/TEST/TEMPFILE/TEMP.256.875908789' to '/u02/asm_backup/temp01.dbf';
4> switch tempfile all;
5> }

executing command: SET NEWNAME

renamed temporary file 1 to /u02/asm_backup/temp01.dbf in control file
starting full resync of recovery catalog
full resync complete

9.禁用闪回数据库并重新启动闪回数据库让闪回日志创建在ASM中

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.


10.如果迁移的是主库,打开数据库

SQL> alter database open;

Database altered.

如果是备库,恢复管理恢复模式

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

11.对于主库,要迁移联机重做日志文件是通过增加新的重做日志文件并存储在文件系统中并删除旧的重做日志文件。对于备库,就是删除旧的备重做日志并增加新的备重做日志并存储在文件系统中。

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         1          1         99   52428800          1 NO       CURRENT                1292113 2015/4/1 20
         2          1         98   52428800          1 YES      INACTIVE               1290567 2015/4/1 20
         3          1          0   52428800          1 YES      UNUSED                       0


SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 '/u02/asm_backup/redo02.log' size 50M;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 '/u02/asm_backup/redo03.log' size 50M;

Database altered.

SQL> alter system switch logfile;

System altered.


SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance test (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '+DISK1/redo01.log'

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '/u02/asm_backup/redo01.log' size 50M;

Database altered.

RMAN同字节序跨平台跨版本迁移数据库(二)

在目标平台执行数据文件转换
将32位windows平台上的数据库jingyong(10.2.0.1)迁移到32位的Linux平台上(10.2.0.5)在目标平台上执行RMAN的convert database操作的步骤如下:
1.将源数据库以只读模式打开

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 3月 31 20:30:23 2015

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


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  419430400 bytes
Fixed Size                  1249320 bytes
Variable Size             125833176 bytes
Database Buffers          285212672 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
SQL> alter database open read only;

数据库已更改。

2.对源数据库执行dbms_tdb.check_db检查

SQL> set serveroutput on
SQL> declare
  2   db_ready boolean;
  3  begin
  4   db_ready :=dbms_tdb.check_db('Linux IA (32-bit)',dbms_tdb.skip_readonly);
  5  end;
  6  /

PL/SQL procedure successfully completed

3.对源数据库执行dbms_tdb.check_external来识别外部对象

SQL> set serveroutput on;
SQL> declare
  2    external boolean;
  3  begin
  4    external:=dbms_tdb.check_external;
  5  end;
  6  /

The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.TEST_DUMP, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR, SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.WORK_DIR, SYS.ADMIN_DIR, SYS.DATA_PUMP_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA

PL/SQL procedure successfully completed

4.在源平台上执行convert database命令并指定on target platform参数。

C:\Users\Administrator>rman target/

恢复管理器: Release 10.2.0.1.0 - Production on 星期二 3月 31 21:05:58 2015

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

连接到目标数据库: JINGYONG (DBID=3213417256)

RMAN> convert database on target platform
2> convert script 'E:\oradata\convert\convertscript.rman'
3> transport script 'E:\oradata\convert\transportscript.sql'
4> new database 'cs'
5> format 'E:\oradata\convert\%U';

启动 convert 于 31-3月 -15
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=152 devtype=DISK

在数据库中找到外部表 SH.SALES_TRANSACTIONS_EXT

在数据库中找到目录 SYS.TEST_DUMP
在数据库中找到目录 SYS.SUBDIR
在数据库中找到目录 SYS.XMLDIR
在数据库中找到目录 SYS.MEDIA_DIR
在数据库中找到目录 SYS.LOG_FILE_DIR
在数据库中找到目录 SYS.DATA_FILE_DIR
在数据库中找到目录 SYS.WORK_DIR
在数据库中找到目录 SYS.ADMIN_DIR
在数据库中找到目录 SYS.DATA_PUMP_DIR

在数据库中找到 BFILE PM.PRINT_MEDIA

在口令文件中找到用户 SYS (具有 SYSDBA and SYSOPER 权限)
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00001 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_SYSTEM_BKLLOT8Z_.
DBF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:00
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00003 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_SYSAUX_BKLLOTFL_.
DBF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:01
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00005 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_EXAMPLE_BKLLRKG2_
.DBF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:00
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00006 name=E:\ORADATA\JINGYONG\TSPITR01.DBF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:00
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00007 name=E:\ORADATA\JINGYONG\TEST01.DBF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:00
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00002 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_UNDOTBS1_BKLLOTM0
_.DBF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:00
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00004 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_USERS_BKLLOTM6_.D
BF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:01
在目标平台上运行 SQL 脚本 E:\ORADATA\CONVERT\TRANSPORTSCRIPT.SQL 以创建数据库
编辑 init.ora 文件 E:\ORADATA\CONVERT\INIT_00Q3836C_1_0.ORA。此 PFILE 将用于在目
标平台上创建数据库据
在目标平台上运行 RMAN 脚本 E:\ORADATA\CONVERT\CONVERTSCRIPT.RMAN 以转换数据文件
要重新编译所有 PL/SQL 模块, 请在目标平台上运行 utlirp.sql 和 utlrp.sql
要更改内部数据库标识符, 请使用 DBNEWID 实用程序
完成 backup 于 31-3月 -15

与在源平台执行convert database命令一样,convert database on target platform会生成可以在目标平台上创建新数据库的传输脚本和包含与源数据库有相同参数设置的PFILE参数文件。convert database on target platform也会生成一个包含对源数据库每个数据文件执行convert datafile命令的转换脚本。源数据库的数据文件必须在不被转换的情况下传输到目标主机相应的目录中,并对转换脚本做相应的修改,转换脚本内容如下:

RUN {

  CONVERT DATAFILE 'E:\ORADATA\JINGYONG\DATAFILE\O1_MF_SYSTEM_BKLLOT8Z_.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'E:\ORADATA\CONVERT\DATA_D-JINGYONG_I-3213417256_TS-SYSTEM_FNO-1_08Q3836D';


  CONVERT DATAFILE 'E:\ORADATA\JINGYONG\DATAFILE\O1_MF_SYSAUX_BKLLOTFL_.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'E:\ORADATA\CONVERT\DATA_D-JINGYONG_I-3213417256_TS-SYSAUX_FNO-3_09Q3836D';


  CONVERT DATAFILE 'E:\ORADATA\JINGYONG\DATAFILE\O1_MF_EXAMPLE_BKLLRKG2_.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'E:\ORADATA\CONVERT\DATA_D-JINGYONG_I-3213417256_TS-EXAMPLE_FNO-5_0AQ3836E';


  CONVERT DATAFILE 'E:\ORADATA\JINGYONG\TSPITR01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'E:\ORADATA\CONVERT\DATA_D-JINGYONG_I-3213417256_TS-TSPITR_FNO-6_0BQ3836E';


  CONVERT DATAFILE 'E:\ORADATA\JINGYONG\TEST01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'E:\ORADATA\CONVERT\DATA_D-JINGYONG_I-3213417256_TS-TEST_FNO-7_0CQ3836E';


  CONVERT DATAFILE 'E:\ORADATA\JINGYONG\DATAFILE\O1_MF_UNDOTBS1_BKLLOTM0_.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'E:\ORADATA\CONVERT\DATA_D-JINGYONG_I-3213417256_TS-UNDOTBS1_FNO-2_0DQ3836E';


  CONVERT DATAFILE 'E:\ORADATA\JINGYONG\DATAFILE\O1_MF_USERS_BKLLOTM6_.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'E:\ORADATA\CONVERT\DATA_D-JINGYONG_I-3213417256_TS-USERS_FNO-4_0EQ3836F';


}

5.在源数据库为只读状态时将数据文件与传输脚本,转换脚本与PFILE参数文件复制到目标主机对应的目录中。

ftp> put CONVERTSCRIPT.RMAN
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 1462 字节,用时 0.00秒 1462000.00千字节/秒。
ftp> put INIT_00Q3836C_1_0.ORA
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 1309 字节,用时 0.00秒 1309.00千字节/秒。
ftp> TRANSPORTSCRIPT.SQL
无效命令。
ftp> put TRANSPORTSCRIPT.SQL
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 2739 字节,用时 0.00秒 2739.00千字节/秒。
ftp> lcd E:\oradata\JINGYONG
目前的本地目录 E:\oradata\JINGYONG。
ftp> bin
200 Switching to Binary mode.
ftp> put TEST01.dbf
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 52436992 字节,用时 2.55秒 20555.47千字节/秒。
ftp> put TSPITR01.dbf
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 52436992 字节,用时 6.71秒 7819.41千字节/秒。
ftp> lcd E:\oradata\JINGYONG\DATAFILE
目前的本地目录 E:\oradata\JINGYONG\DATAFILE。
ftp> bin
200 Switching to Binary mode.
ftp> put O1_MF_SYSTEM_BKLLOT8Z_.
O1_MF_SYSTEM_BKLLOT8Z_.: ftp> DBF
无效命令。
ftp> put O1_MF_EXAMPLE_BKLLRKG2_.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 104865792 字节,用时 12.16秒 8620.29千字节/秒。
ftp> put O1_MF_SYSAUX_BKLLOTFL_.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 251666432 字节,用时 25.59秒 9835.71千字节/秒。
ftp> put O1_MF_SYSTEM_BKLLOT8Z_.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 503324672 字节,用时 60.46秒 8325.06千字节/秒。
ftp> put O1_MF_UNDOTBS1_BKLLOTM0_.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 31465472 字节,用时 6.67秒 4718.17千字节/秒。
ftp> put O1_MF_USERS_BKLLOTM6_.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 5251072 字节,用时 0.42秒 12355.46千字节/秒。


[oracle@oracle11g cs]$ ls -lrt
total 978980
-rwxrwxrwx 1 oracle oinstall      1462 Mar 31 21:22 CONVERTSCRIPT.RMAN
-rwxrwxrwx 1 oracle oinstall      1309 Mar 31 21:22 INIT_00Q3836C_1_0.ORA
-rwxrwxrwx 1 oracle oinstall      2739 Mar 31 21:22 TRANSPORTSCRIPT.SQL
-rwxrwxrwx 1 oracle oinstall  52436992 Mar 31 21:23 TEST01.DBF
-rwxrwxrwx 1 oracle oinstall  52436992 Mar 31 21:23 TSPITR01.DBF
-rwxrwxrwx 1 oracle oinstall 104865792 Mar 31 21:24 O1_MF_EXAMPLE_BKLLRKG2_.DBF
-rwxrwxrwx 1 oracle oinstall 251666432 Mar 31 21:25 O1_MF_SYSAUX_BKLLOTFL_.DBF
-rwxrwxrwx 1 oracle oinstall 503324672 Mar 31 21:26 O1_MF_SYSTEM_BKLLOT8Z_.DBF
-rwxrwxrwx 1 oracle oinstall  31465472 Mar 31 21:27 O1_MF_UNDOTBS1_BKLLOTM0_.DBF
-rwxrwxrwx 1 oracle oinstall   5251072 Mar 31 21:27 O1_MF_USERS_BKLLOTM6_.DBF

6.创建新数据库cs相关目录,并修改PFILE参数文件,传输脚本与转换脚本中的相关路径信息,因为convert database on target platform命令并不会生成转换后的数据文件副本。当目标主机数据文件存储路径与源数据库数据文件存储路径完全相同时,可以直接执行转换脚本,但这里由于目标主机存储数据文件的路径与源数据库存储数据文件的路径不相同,需要对转换脚本做相应修改,也需要对用来创建新数据库的传输脚本做修改。

创建相关目录

[oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/cs/adump
[oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/cs/bdump
[oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/cs/cdump
[oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/cs/udump
[oracle@oracle11g dbs]$ chmod -R 777 /u01/app/oracle/admin/cs*

修改PFILE参数文件

[oracle@oracle11g cs]$ mv INIT_00Q3836C_1_0.ORA initcs.ora
[oracle@oracle11g cs]$ vi initcs.ora
  __shared_pool_size       = 113246208

  __large_pool_size        = 4194304

  __java_pool_size         = 4194304

  __streams_pool_size      = 4194304

  __db_cache_size          = 285212672

  remote_login_passwordfile= "EXCLUSIVE"

  db_domain                = ""
  db_create_file_dest      = "E:\ORADATA\CONVERT\oradata"
# Please change the values of the following parameters:


  audit_file_dest          = '/u01/app/oracle/admin/cs/adump'

  background_dump_dest     = '/u01/app/oracle/admin/cs/bdump'

  user_dump_dest           = '/u01/app/oracle/admin/cs/udump'

  core_dump_dest           = '/u01/app/oracle/admin/cs/cdump'

  db_name                  = "CS"

  control_files='/u02/cs/control01.ctl'

# Please review the values of the following parameters:


  dispatchers              = "(PROTOCOL=TCP) (SERVICE=csXDB)"



# The values of the following parameters are from source database:

  processes                = 150

  nls_language             = "SIMPLIFIED CHINESE"

  nls_territory            = "CHINA"

  sga_target               = 130M

  db_block_size            = 8192

  compatible               = "10.2.0.1.0"

  db_file_multiblock_read_count= 16

  undo_management          = "AUTO"

  undo_tablespace          = "UNDOTBS1"

  job_queue_processes      = 10

  open_cursors             = 300

  pga_aggregate_target     = 32M

"initcs.ora" 48L, 990C written
[oracle@oracle11g cs]$ cp initcs.ora $ORACLE_HOME/dbs

修改转换脚本

[oracle@oracle11g cs]$ vi CONVERTSCRIPT.RMAN
RUN {

  CONVERT DATAFILE '/u02/cs/O1_MF_SYSTEM_BKLLOT8Z_.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u02/cs/datafiles/system01.dbf';


  CONVERT DATAFILE '/u02/cs/O1_MF_SYSAUX_BKLLOTFL_.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u02/cs/datafiles/sysaux01.dbf';


  CONVERT DATAFILE '/u02/cs/O1_MF_EXAMPLE_BKLLRKG2_.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u02/cs/datafiles/example01.dbf';


  CONVERT DATAFILE '/u02/cs/TSPITR01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u02/cs/datafiles/tspitr01.dbf';


  CONVERT DATAFILE '/u02/cs/TEST01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u02/cs/datafiles/test01.dbf';


  CONVERT DATAFILE '/u02/cs/O1_MF_UNDOTBS1_BKLLOTM0_.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u02/cs/datafiles/undotsb1.dbf';


  CONVERT DATAFILE '/u02/cs/O1_MF_USERS_BKLLOTM6_.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u02/cs/datafiles/users01.dbf';


}

修改用来创建新数据库的传输脚本

[oracle@oracle11g cs]$ vi TRANSPORTSCRIPT.SQL
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE='/u01/app/oracle/10.2.0/db/dbs/initcs.ora'

-- Create SPFILE
CREATE SPFILE FROM PFILE = '/u01/app/oracle/10.2.0/db/dbs/initcs.ora';


STARTUP FORCE NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "CS" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/cs/datafiles/redo01.log' SIZE 50M,
  GROUP 2 '/u02/cs/datafiles/redo02.log' SIZE 50M,
  GROUP 3 '/u02/cs/datafiles/redo03.log' SIZE 50M
DATAFILE
  '/u02/cs/datafiles/system01.dbf',
  '/u02/cs/datafiles/sysaux01.dbf',
  '/u02/cs/datafiles/example01.dbf',
  '/u02/cs/datafiles/tspitr01.dbf',
  '/u02/cs/datafiles/test01.dbf',
  '/u02/cs/datafiles/undotsb1.dbf',
  '/u02/cs/datafiles/users01.dbf'
CHARACTER SET ZHS16GBK
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/cs/datafiles/temp01.dbf'
     SIZE 50M  AUTOEXTEND OFF;
-- End of tempfile additions.
--

set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt *    or the global database name for this database. Use the
prompt *    NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE
STARTUP UPGRADE
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql

7.在目标主机上执行转换脚本

[oracle@oracle11g admin]$ rman target/

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 31 21:55:50 2015

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

connected to target database: JINGYONG (DBID=3213417256)

RMAN> run
2> {
3> CONVERT DATAFILE '/u02/cs/O1_MF_SYSTEM_BKLLOT8Z_.DBF'
4>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
5>   FORMAT '/u02/cs/datafiles/system01.dbf';
6>
7>   CONVERT DATAFILE '/u02/cs/O1_MF_SYSAUX_BKLLOTFL_.DBF'
8>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
9>   FORMAT '/u02/cs/datafiles/sysaux01.dbf';
10>
11>   CONVERT DATAFILE '/u02/cs/O1_MF_EXAMPLE_BKLLRKG2_.DBF'
12>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
13>   FORMAT '/u02/cs/datafiles/example01.dbf';
14>
15>   CONVERT DATAFILE '/u02/cs/TSPITR01.DBF'
16>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
17>   FORMAT '/u02/cs/datafiles/tspitr01.dbf';
18>
19>   CONVERT DATAFILE '/u02/cs/TEST01.DBF'
20>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
21>   FORMAT '/u02/cs/datafiles/test01.dbf';
22>
23>   CONVERT DATAFILE '/u02/cs/O1_MF_UNDOTBS1_BKLLOTM0_.DBF'
24>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
25>   FORMAT '/u02/cs/datafiles/undotsb1.dbf';
26>
27>   CONVERT DATAFILE '/u02/cs/O1_MF_USERS_BKLLOTM6_.DBF'
28>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
29>   FORMAT '/u02/cs/datafiles/users01.dbf';
30> }

Starting backup at 31-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/u02/cs/O1_MF_SYSTEM_BKLLOT8Z_.DBF
converted datafile=/u02/cs/datafiles/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
Finished backup at 31-MAR-15

Starting backup at 31-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u02/cs/O1_MF_SYSAUX_BKLLOTFL_.DBF
converted datafile=/u02/cs/datafiles/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
Finished backup at 31-MAR-15

Starting backup at 31-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u02/cs/O1_MF_EXAMPLE_BKLLRKG2_.DBF
converted datafile=/u02/cs/datafiles/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
Finished backup at 31-MAR-15

Starting backup at 31-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u02/cs/TSPITR01.DBF
converted datafile=/u02/cs/datafiles/tspitr01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished backup at 31-MAR-15

Starting backup at 31-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u02/cs/TEST01.DBF
converted datafile=/u02/cs/datafiles/test01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished backup at 31-MAR-15

Starting backup at 31-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u02/cs/O1_MF_UNDOTBS1_BKLLOTM0_.DBF
converted datafile=/u02/cs/datafiles/undotsb1.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 31-MAR-15

Starting backup at 31-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u02/cs/O1_MF_USERS_BKLLOTM6_.DBF
converted datafile=/u02/cs/datafiles/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 31-MAR-15

8.执行传输脚本中的内容来创建新数据库(也可以直接执行传输脚本)

[oracle@oracle11g admin]$ export ORACLE_SID=cs
[oracle@oracle11g admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 31 22:01:12 2015

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

Connected to an idle instance.

SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/10.2.0/db/dbs/initcs.ora'
ORACLE instance started.

Total System Global Area  138412032 bytes
Fixed Size                  1272432 bytes
Variable Size              62915984 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes

SQL> CREATE CONTROLFILE REUSE SET DATABASE "CS" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u02/cs/datafiles/redo01.log' SIZE 50M,
  9    GROUP 2 '/u02/cs/datafiles/redo02.log' SIZE 50M,
 10    GROUP 3 '/u02/cs/datafiles/redo03.log' SIZE 50M
 11  DATAFILE
 12    '/u02/cs/datafiles/system01.dbf',
 13    '/u02/cs/datafiles/sysaux01.dbf',
 14    '/u02/cs/datafiles/example01.dbf',
 15    '/u02/cs/datafiles/tspitr01.dbf',
 16    '/u02/cs/datafiles/test01.dbf',
 17    '/u02/cs/datafiles/undotsb1.dbf',
 18    '/u02/cs/datafiles/users01.dbf'
 19  CHARACTER SET ZHS16GBK
 20  ;

Control file created.


SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

在执行恢复后正常打开数据库实例被终止,在alert.log中的错误信息如下:

Errors in file /u01/app/oracle/admin/cs/udump/cs_ora_6458.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Tue Mar 31 22:04:28 CST 2015
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 6458
ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS...

错误信息指示需要以升级模式来启动数据库,因为迁移过来的源数据库的版本是10.2.0.1,而目标主机上的数据库版本是10.2.0.5。

以升级模式启动数据库

[oracle@oracle11g admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 31 22:06:55 2015

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

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  138412032 bytes
Fixed Size                  1272432 bytes
Variable Size              62915984 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
ORA-01113: file 1 needs media recovery if it was restored from backup, or END
BACKUP if it was not
ORA-01110: data file 1: '/u02/cs/datafiles/system01.dbf'

执行恢复并关闭数据库

SQL> recover database;
Media recovery complete.
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

再次以升级模式启动数据库

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  138412032 bytes
Fixed Size                  1272432 bytes
Variable Size              62915984 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.

创建临时表空间

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/cs/datafiles/temp01.dbf'
  2       SIZE 50M  AUTOEXTEND OFF;

Tablespace altered.

SQL>@$ORACLE_HOME/rdbms/admin/utlirp.sql
省略...
SQL>
SQL> Rem Continue even if there are SQL errors
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlip.sql
SQL> Rem ===========================================================================
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   utlirp.sql completed successfully. All PL/SQL objects in the
DOC>   database have been invalidated.
DOC>
DOC>   Shut down and restart the database in normal mode and run utlrp.sql to
DOC>   recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

上面的输出信息说在以正常模式重启数据库后执行utlrp.sql脚本来编译无效对象。由于这里源数据库是10.2.0.1,而目标主机数据库的版本是10.2.0.5,在重启数据库仍然需要以升级模式来启动并执行utlrp.sql脚本

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

Total System Global Area  138412032 bytes
Fixed Size                  1272432 bytes
Variable Size              62915984 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> Rem ===========================================================================
SQL> Rem BEGIN utlrp.sql
SQL> Rem ===========================================================================
省略...

PL/SQL procedure successfully completed.

SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================

执行和数据库升级相关的脚本catupgrade.sql

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
省略...
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END  2015-03-31 23:04:46
.
Oracle Database 10.2 Upgrade Status Utility           03-31-2015 23:04:46
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                    VALID      10.2.0.5.0  00:18:17
JServer JAVA Virtual Machine              VALID      10.2.0.5.0  00:04:52
Oracle XDK                                VALID      10.2.0.5.0  00:00:49
Oracle Database Java Packages             VALID      10.2.0.5.0  00:00:32
Oracle Text                               VALID      10.2.0.5.0  00:00:56
Oracle XML Database                       VALID      10.2.0.5.0  00:02:33
Oracle Workspace Manager                  VALID      10.2.0.5.0  00:01:05
Oracle Data Mining                      INVALID      10.2.0.5.0  00:00:30
OLAP Analytic Workspace                   VALID      10.2.0.5.0  00:01:07
OLAP Catalog                              VALID      10.2.0.5.0  00:01:32
Oracle OLAP API                           VALID      10.2.0.5.0  00:01:08
Oracle interMedia                         VALID      10.2.0.5.0  00:05:11
Spatial                                   VALID      10.2.0.5.0  00:06:40
Oracle Expression Filter                INVALID      10.2.0.5.0  00:00:25
Oracle Enterprise Manager                 VALID      10.2.0.5.0  00:02:50
Oracle Rule Manager                     INVALID      10.2.0.5.0  00:00:20
.
Total Upgrade Time: 00:51:07
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above PL/SQL lists the SERVER components in the upgraded
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the current version,
DOC>   consult the Oracle Database Upgrade Guide for troubleshooting
DOC>   recommendations.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>

再次运行utlrp.sql脚本来进行编译无效对象

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2015-03-31 23:05:21
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2015-03-31 23:07:54
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  5
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0
SQL> column comp_name format a35
SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_server_registry order by modified;

COMP_NAME                           STATUS                 VERSION
----------------------------------- ---------------------- --------------------
Oracle Enterprise Manager           VALID                  10.2.0.5.0
Oracle Database Packages and Types  VALID                  10.2.0.5.0
Oracle Workspace Manager            VALID                  10.2.0.5.0
JServer JAVA Virtual Machine        VALID                  10.2.0.5.0
Oracle Database Java Packages       VALID                  10.2.0.5.0
Oracle Database Catalog Views       VALID                  10.2.0.5.0
Oracle XDK                          VALID                  10.2.0.5.0
Oracle Expression Filter            VALID                  10.2.0.5.0
Oracle Data Mining                  VALID                  10.2.0.5.0
Oracle Text                         VALID                  10.2.0.5.0
Oracle Rule Manager                 VALID                  10.2.0.5.0
Oracle interMedia                   VALID                  10.2.0.5.0
Oracle XML Database                 VALID                  10.2.0.5.0
OLAP Analytic Workspace             VALID                  10.2.0.5.0
Oracle OLAP API                     VALID                  10.2.0.5.0
OLAP Catalog                        VALID                  10.2.0.5.0
Spatial                             VALID                  10.2.0.5.0

RMAN同字节序跨平台跨版本迁移数据库(一)

跨平台传输数据库
RMAN的convert database命令被用来将整个数据库从一个平台移动到另一个平台。当源平台与目标平台字节序相同时,RMAN会在目标平台自动完成创建新数据库的大部分操作。当跨平台传输数据库时convertdatabase命令的处理过程将会很长。与传输表空间一样,RMAN的convert database命令可以在源平台或目标平台执行。

当跨平台传输数据库时文件会自动被传输到目标平台,这些文件包括:
1.属于永久表空间的数据文件,注意,不管源平台与目标平吧的字节序是否相同,传输数据库的数据文件必须要进行转换不能简单的从一个平台复制到另一个平台。与跨平台传输表空间不一样,传输整个数据库要求特定类型的数据块,比如undo段,要被重新格式化确保与目标平台兼容

2.如果使用PFILE,PFILE文件会被传输。如果使用SPFILE,会基于SPFILE生成PFILE并将其传输,并在目标平基于PFILE来生成新的SPFILE。注意,在大多数情况下,PFILE文件中的有些参数对于新数据库需要手动修改。例如db_name,control_files

跨平台传输数据库的限制
跨平台传输数据库的主要限制是源平台与目标平台必须有相同的字节序。例如,当将数据库从Windows平台传输到Linux平台(都是小字节序)或者从HP-UX到AIX(都是大字节序),不能将整个数据库从HP-UX传输到Linux平台上。然而,可以在目标平台上创建一个新数据库,并使用表空间传输功能将需要的表空间从源数据库中使用跨平台与跨字节序传输到目标数据库中。

在源平台执行转换
将32位windows平台上的数据库jingyong(10.2.0.1)迁移到32位的Linux平台上(10.2.0.5)。在源平台上执行RMAN的convert database操作的步骤如下:
1.将源数据库以只读模式打开

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 3月 31 15:08:36 2015

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


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  419430400 bytes
Fixed Size                  1249320 bytes
Variable Size             125833176 bytes
Database Buffers          285212672 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
SQL> alter database open read only;

数据库已更改。

2.对源数据库执行dbms_tdb.check_db检查

SQL> set serveroutput on
SQL> declare
  2   db_ready boolean;
  3  begin
  4   db_ready :=dbms_tdb.check_db('Linux IA (32-bit)',dbms_tdb.skip_readonly);
  5  end;
  6  /

PL/SQL procedure successfully completed

3.对源数据库执行dbms_tdb.check_external来识别外部对象

SQL> set serveroutput on;
SQL> declare
  2    external boolean;
  3  begin
  4    external:=dbms_tdb.check_external;
  5  end;
  6  /

The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.TEST_DUMP, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR, SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.WORK_DIR, SYS.ADMIN_DIR, SYS.DATA_PUMP_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA

PL/SQL procedure successfully completed

4.执行convert database命令,指定目标平台和输出文件名称。RMAN会生成需要移动到目标平台上的文件,包含以下文件:
–数据库完整的数据文件副本
–在目标平台使用的新的PFILE参数文件,包含来自源数据库PFILE或SPFILE中的设置。在目标平台使用之前需要进行相应修改
–传输脚本,包含在目标平台创建新数据库的SQL语句

RMAN> convert database new database 'jingyong'
2> transport script 'E:\oradata\transport\transport_db_script.sql'
3> to platform 'Linux IA (32-bit)'
4> db_file_name_convert('E:\oradata\JINGYONG\DATAFILE', 'E:\oradata\transport','
E:\oradata\JINGYONG', 'E:\oradata\transport');

启动 convert 于 31-3月 -15
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=152 devtype=DISK

在数据库中找到外部表 SH.SALES_TRANSACTIONS_EXT

在数据库中找到目录 SYS.TEST_DUMP
在数据库中找到目录 SYS.SUBDIR
在数据库中找到目录 SYS.XMLDIR
在数据库中找到目录 SYS.MEDIA_DIR
在数据库中找到目录 SYS.LOG_FILE_DIR
在数据库中找到目录 SYS.DATA_FILE_DIR
在数据库中找到目录 SYS.WORK_DIR
在数据库中找到目录 SYS.ADMIN_DIR
在数据库中找到目录 SYS.DATA_PUMP_DIR

在数据库中找到 BFILE PM.PRINT_MEDIA

在口令文件中找到用户 SYS (具有 SYSDBA and SYSOPER 权限)
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00001 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_SYSTEM_BKLLOT8Z_.
DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\O1_MF_SYSTEM_BKLLOT8Z_.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:25
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00003 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_SYSAUX_BKLLOTFL_.
DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\O1_MF_SYSAUX_BKLLOTFL_.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:16
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00005 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_EXAMPLE_BKLLRKG2_
.DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\O1_MF_EXAMPLE_BKLLRKG2_.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:07
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00006 name=E:\ORADATA\JINGYONG\TSPITR01.DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\TSPITR01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:03
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00007 name=E:\ORADATA\JINGYONG\TEST01.DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\TEST01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:03
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00002 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_UNDOTBS1_BKLLOTM0
_.DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\O1_MF_UNDOTBS1_BKLLOTM0_.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:03
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00004 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_USERS_BKLLOTM6_.D
BF
已转换的数据文件 = E:\ORADATA\TRANSPORT\O1_MF_USERS_BKLLOTM6_.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
在目标平台上运行 SQL 脚本 E:\ORADATA\TRANSPORT\TRANSPORT_DB_SCRIPT.SQL 以创建数
据库
编辑 init.ora 文件 D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00Q37GUS_1_0.ORA
。此 PFILE 将用于在目标平台上创建数据库据
要重新编译所有 PL/SQL 模块, 请在目标平台上运行 utlirp.sql 和 utlrp.sql
要更改内部数据库标识符, 请使用 DBNEWID 实用程序
完成 backup 于 31-3月 -15

5.将转换操作生成的所有文件传输到目标主机上存储数据库文件的目录中当convert database执行完成后,源数据库可以以读写模式打开,生成的所有文件都要被传输到目标主机上。

ftp> put INIT_00Q37GUS_1_0.ORA
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 1415 字节,用时 0.00秒 1415.00千字节/秒。
ftp> put O1_MF_EXAMPLE_BKLLRKG2_.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 104865792 字节,用时 8.57秒 12233.53千字节/秒。
ftp> put O1_MF_SYSTEM_BKLLOT8Z_.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 503324672 字节,用时 49.54秒 10160.38千字节/秒。
ftp> put TSPITR01.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 52436992 字节,用时 4.46秒 11762.45千字节/秒。
ftp> put TEST01.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 52436992 字节,用时 3.48秒 15050.80千字节/秒。
ftp> put O1_MF_UNDOTBS1_BKLLOTM0_.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 31465472 字节,用时 2.05秒 15341.53千字节/秒。
ftp> put O1_MF_USERS_BKLLOTM6_.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 5251072 字节,用时 0.20秒 26520.57千字节/秒。
ftp> put TRANSPORT_DB_SCRIPT.SQL
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 2582 字节,用时 0.00秒 2582000.00千字节/秒。
ftp> put O1_MF_SYSAUX_BKLLOTFL_.DBF
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 发送 251666432 字节,用时 19.63秒 12817.89千字节/秒。


[oracle@oracle11g jingyong]$ ls -lrt
total 978976
-rwxrwxrwx 1 oracle oinstall      1415 Mar 31 16:18 INIT_00Q37GUS_1_0.ORA
-rwxrwxrwx 1 oracle oinstall 104865792 Mar 31 16:18 O1_MF_EXAMPLE_BKLLRKG2_.DBF
-rwxrwxrwx 1 oracle oinstall 503324672 Mar 31 16:20 O1_MF_SYSTEM_BKLLOT8Z_.DBF
-rwxrwxrwx 1 oracle oinstall  52436992 Mar 31 16:21 TSPITR01.DBF
-rwxrwxrwx 1 oracle oinstall  52436992 Mar 31 16:21 TEST01.DBF
-rwxrwxrwx 1 oracle oinstall  31465472 Mar 31 16:22 O1_MF_UNDOTBS1_BKLLOTM0_.DBF
-rwxrwxrwx 1 oracle oinstall   5251072 Mar 31 16:22 O1_MF_USERS_BKLLOTM6_.DBF
-rwxrwxrwx 1 oracle oinstall      2582 Mar 31 16:23 TRANSPORT_DB_SCRIPT.SQL
-rwxrwxrwx 1 oracle oinstall 251666432 Mar 31 16:24 O1_MF_SYSAUX_BKLLOTFL_.DBF

6.在目标主机上创建数据库需要的目录,并修改PFILE参数文件中的相关参数,并修改传输脚本中指示数据文件的位置。

创建相关目录

[oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/jingyong/adump
[oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/jingyong/bdump
[oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/jingyong/cdump
[oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/jingyong/udump
[oracle@oracle11g dbs]$ chmod -R 777 /u01/app/oracle/admin/jingyong*

修改PFILE参数文件

[oracle@oracle11g jingyong]$ vi initjingyong.ora
# Please change the values of the following parameters:


  audit_file_dest          = '/u01/app/oracle/admin/jingyong/adump'

  background_dump_dest     = '/u01/app/oracle/admin/jingyong/bdump'

  user_dump_dest           = '/u01/app/oracle/admin/jingyong/udump'

  core_dump_dest           = '/u01/app/oracle/admin/jingyong/udump'

  db_name                  = "JINGYONG"
  control_files='/u01/app/oracle/oradata/test/control01.ctl'


# Please review the values of the following parameters:


  dispatchers              = "(PROTOCOL=TCP) (SERVICE=jingyongXDB)"



# The values of the following parameters are from source database:

  processes                = 150

  nls_language             = "SIMPLIFIED CHINESE"

  nls_territory            = "CHINA"

  sga_target               = 130M

  db_block_size            = 8192

  compatible               = "10.2.0.1.0"

  db_file_multiblock_read_count= 16

  undo_management          = "AUTO"

  undo_tablespace          = "UNDOTBS1"

  job_queue_processes      = 10

  open_cursors             = 300

  pga_aggregate_target     = 32M

修改convert database命令生成的传输脚本

[oracle@oracle11g jingyong]$ vi TRANSPORT_DB_SCRIPT.SQL
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE='/u01/app/oracle/10.2.0/db/dbs/initjingyong.ora'

-- Create SPFILE
CREATE SPFILE FROM PFILE = '/u01/app/oracle/10.2.0/db/dbs/initjingyong.ora';


STARTUP FORCE NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "JINGYONG" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/jingyong/redo01.log' SIZE 50M,
  GROUP 2 '/u02/jingyong/redo02.log' SIZE 50M,
  GROUP 3 '/u02/jingyong/redo03.log' SIZE 50M
DATAFILE
  '/u02/jingyong/O1_MF_SYSTEM_BKLLOT8Z_.DBF',
  '/u02/jingyong/O1_MF_UNDOTBS1_BKLLOTM0_.DBF',
  '/u02/jingyong/O1_MF_SYSAUX_BKLLOTFL_.DBF',
  '/u02/jingyong/O1_MF_USERS_BKLLOTM6_.DBF',
  '/u02/jingyong/O1_MF_EXAMPLE_BKLLRKG2_.DBF',
  '/u02/jingyong/TSPITR01.DBF',
  '/u02/jingyong/TEST01.DBF'
CHARACTER SET ZHS16GBK
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/jingyong/temp01.dbf'
     SIZE 50M  AUTOEXTEND OFF;
-- End of tempfile additions.
--

set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt *    or the global database name for this database. Use the
prompt *    NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE
STARTUP UPGRADE
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql



7.使用SQL*Plus来执行传输脚本中的内容在目标主机上创建新的数据库

[oracle@oracle11g jingyong]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 31 18:11:36 2015

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

Connected to an idle instance.

SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/10.2.0/db/dbs/initjingyong.ora'
ORACLE instance started.

Total System Global Area  138412032 bytes
Fixed Size                  1272432 bytes
Variable Size              62915984 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
SQL> CREATE CONTROLFILE REUSE SET DATABASE "JINGYONG" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u02/jingyong/redo01.log' SIZE 50M,
  9    GROUP 2 '/u02/jingyong/redo02.log' SIZE 50M,
 10    GROUP 3 '/u02/jingyong/redo03.log' SIZE 50M
 11  DATAFILE
 12    '/u02/jingyong/O1_MF_SYSTEM_BKLLOT8Z_.DBF',
 13    '/u02/jingyong/O1_MF_UNDOTBS1_BKLLOTM0_.DBF',
 14    '/u02/jingyong/O1_MF_SYSAUX_BKLLOTFL_.DBF',
 15    '/u02/jingyong/O1_MF_USERS_BKLLOTM6_.DBF',
 16    '/u02/jingyong/O1_MF_EXAMPLE_BKLLRKG2_.DBF',
 17    '/u02/jingyong/TSPITR01.DBF',
 18    '/u02/jingyong/TEST01.DBF'
 19  CHARACTER SET ZHS16GBK
 20  ;

Control file created.

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

在以open resetlogs方式打开数据库是出错了,alert.log中的错误信息如下所示:

Errors in file /u01/app/oracle/admin/jingyong/udump/jingyong_ora_5406.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 5406
ORA-1092 signalled during: alter database open...

错误信息指示需要以升级模式来启动数据库,因为迁移过来的源数据库的版本是10.2.0.1,而目标主机上的数据库版本是10.2.0.5。

以升级模式启动数据库

[oracle@oracle11g jingyong]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 31 18:11:36 2015

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

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  138412032 bytes
Fixed Size                  1272432 bytes
Variable Size              62915984 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
ORA-01113: file 1 needs media recovery if it was restored from backup, or END
BACKUP if it was not
ORA-01110: data file 1: '/u02/jingyong/O1_MF_SYSTEM_BKLLOT8Z_.DBF'

执行恢复并关闭数据库

SQL> recover database;
Media recovery complete.
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

再次以升级模式启动数据库

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  138412032 bytes
Fixed Size                  1272432 bytes
Variable Size              62915984 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.

创建临时表空间

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/jingyong/temp01.dbf'
  2       SIZE 50M  AUTOEXTEND OFF;

SQL>@$ORACLE_HOME//rdbms/admin/utlirp.sql
省略...
SQL>
SQL> Rem Continue even if there are SQL errors
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlip.sql
SQL> Rem ===========================================================================
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   utlirp.sql completed successfully. All PL/SQL objects in the
DOC>   database have been invalidated.
DOC>
DOC>   Shut down and restart the database in normal mode and run utlrp.sql to
DOC>   recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

上面的输出信息说在以正常模式重启数据库后执行utlrp.sql脚本来编译无效对象。由于这里源数据库是10.2.0.1,而目标主机数据库的版本是10.2.0.5,在重启数据库仍然需要以升级模式来启动并执行utlrp.sql脚本

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

Total System Global Area  138412032 bytes
Fixed Size                  1272432 bytes
Variable Size              62915984 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> Rem ===========================================================================
SQL> Rem BEGIN utlrp.sql
SQL> Rem ===========================================================================
省略...

PL/SQL procedure successfully completed.

SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================

执行和数据库升级相关的脚本catupgrade.sql

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
省略...
COMP_TIMESTAMP UPGRD_END  2015-03-31 20:42:53
.
Oracle Database 10.2 Upgrade Status Utility           03-31-2015 20:42:54
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                    VALID      10.2.0.5.0  00:18:20
JServer JAVA Virtual Machine              VALID      10.2.0.5.0  00:05:21
Oracle XDK                                VALID      10.2.0.5.0  00:00:49
Oracle Database Java Packages             VALID      10.2.0.5.0  00:00:37
Oracle Text                               VALID      10.2.0.5.0  00:01:02
Oracle XML Database                       VALID      10.2.0.5.0  00:02:47
Oracle Workspace Manager                  VALID      10.2.0.5.0  00:01:39
Oracle Data Mining                        VALID      10.2.0.5.0  00:00:39
OLAP Analytic Workspace                   VALID      10.2.0.5.0  00:00:52
OLAP Catalog                              VALID      10.2.0.5.0  00:01:24
Oracle OLAP API                           VALID      10.2.0.5.0  00:01:08
Oracle interMedia                         VALID      10.2.0.5.0  00:05:22
Spatial                                   VALID      10.2.0.5.0  00:07:20
Oracle Expression Filter                  VALID      10.2.0.5.0  00:00:26
Oracle Enterprise Manager                 VALID      10.2.0.5.0  00:02:56
Oracle Rule Manager                       VALID      10.2.0.5.0  00:00:27
.
Total Upgrade Time: 00:53:45
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above PL/SQL lists the SERVER components in the upgraded
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the current version,
DOC>   consult the Oracle Database Upgrade Guide for troubleshooting
DOC>   recommendations.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################

再次运行utlrp.sql脚本来进行编译无效对象

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2015-03-31 20:50:46
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2015-03-31 20:53:25
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                 53
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

Proudly powered by WordPress | Indrajeet by Sus Hill.