【RMAN】RMAN跨版本恢复(上)--小版本异机恢复
BLOG文档结构图
前几天去面试被问到了关于rman是否可以跨版本恢复的问题，其实之前有网友曾经问过只是我没有做实验，这几天有空就研究了下rman跨版本恢复的这个问题。
ORACLE_SID=orcl
原机： OS:Linux x86 64-bit IP:192.168.59.129 oracle：11.2.0.1.0 归档模式
异机： OS:Linux x86 64-bit IP:192.168.59.10 oracle：11.2.0.3.0 归档模式
目的：利用原机的rman备份集将原库恢复到异机。
关于10g的跨小版本恢复参考：http://blog.chinaunix.net/uid-26736162-id-4942816.html ，本文为11g的跨小版本恢复。
关于在不同版本和平台之间进行还原或复制的常见问题 ：http://blog.itpub.net/26736162/viewspace-1549041/
一、 全备份原数据库并拷贝到异机
备份脚本如下：
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database filesperset 4 format '/home/oracle/oracle_bk/orcl/full_%n_%T_%t_%s_%p.bak';
backup spfile format='/home/oracle/oracle_bk/orcl/spfile_%n_%U_%T.bak';
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/oracle_bk/orcl/arch_%d_%T_%s_%p.bak' delete input;
backup current controlfile format '/home/oracle/oracle_bk/orcl/ctl_%d_%T_%s_%p.bak';
release channel c1;
release channel c2;
}
[oracle@rhel6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 9 10:14:24 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile from spfile;
File created.
[oracle@rhel6 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 9 09:37:44 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1379935487)
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> backup database filesperset 4 format '/home/oracle/oracle_bk/orcl/full_%n_%T_%t_%s_%p.bak';
5> sql 'alter system archive log current';
6> backup archivelog all format '/home/oracle/oracle_bk/orcl/arch_%d_%T_%s_%p.bak' delete input;
7> backup current controlfile format '/home/oracle/oracle_bk/orcl/ctl_%d_%T_%s_%p.bak';
8> release channel c1;
9> release channel c2;
10> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=50 device type=DISK
allocated channel: c2
channel c2: SID=17 device type=DISK
Starting backup at 09-APR-15
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/goldengate01.dbf
channel c1: starting piece 1 at 09-APR-15
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel c2: starting piece 1 at 09-APR-15
channel c1: finished piece 1 at 09-APR-15
piece handle=/home/oracle/oracle_bk/orcl/full_ORCLxxxx_20150409_876562667_61_1.bak tag=TAG20150409T093747 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:56
channel c2: finished piece 1 at 09-APR-15
piece handle=/home/oracle/oracle_bk/orcl/full_ORCLxxxx_20150409_876562667_62_1.bak tag=TAG20150409T093747 comment=NONE
channel c2: backup set complete, elapsed time: 00:01:16
Finished backup at 09-APR-15
Starting backup at 09-APR-15
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 09-APR-15
channel ORA_DISK_1: finished piece 1 at 09-APR-15
piece handle=/home/oracle/oracle_bk/orcl/spfile_ORCLxxxx_26q3ujt4_1_1_20150409.bak tag=TAG20150409T100628 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-APR-15
Starting Control File and SPFILE Autobackup at 09-APR-15
piece handle=/home/oracle/oracle_bk/orclasm/control_c-1379935487-20150409-02.bak comment=NONE
Finished Control File and SPFILE Autobackup at 09-APR-15
sql statement: alter system archive log current
Starting backup at 09-APR-15
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=61 STAMP=876562747
channel c1: starting piece 1 at 09-APR-15
channel c2: starting compressed archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=14 RECID=62 STAMP=876562747
channel c2: starting piece 1 at 09-APR-15
channel c1: finished piece 1 at 09-APR-15
piece handle=/home/oracle/oracle_bk/orcl/arch_ORCL_20150409_64_1.bak tag=TAG20150409T093907 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_04_09/o1_mf_1_13_blcp1vb5_.arc RECID=61 STAMP=876562747
channel c2: finished piece 1 at 09-APR-15
piece handle=/home/oracle/oracle_bk/orcl/arch_ORCL_20150409_65_1.bak tag=TAG20150409T093907 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c2: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_04_09/o1_mf_1_14_blcp1vd5_.arc RECID=62 STAMP=876562747
Finished backup at 09-APR-15
Starting backup at 09-APR-15
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 09-APR-15
channel c1: finished piece 1 at 09-APR-15
piece handle=/home/oracle/oracle_bk/orcl/ctl_ORCL_20150409_66_1.bak tag=TAG20150409T093908 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-APR-15
Starting Control File and SPFILE Autobackup at 09-APR-15
piece handle=/home/oracle/oracle_bk/orclasm/control_c-1379935487-20150409-03.bak comment=NONE
Finished Control File and SPFILE Autobackup at 09-APR-15
released channel: c1
released channel: c2
RMAN>
[root@rhel6 ~]# cd /home/oracle/oracle_bk/orcl/
[root@rhel6 orcl]# ll
total 281732
-rw-r-----. 1 oracle asmadmin 3072 Apr 9 09:39 arch_ORCL_20150409_64_1.bak
-rw-r-----. 1 oracle asmadmin 2560 Apr 9 09:39 arch_ORCL_20150409_65_1.bak
-rw-r-----. 1 oracle asmadmin 1114112 Apr 9 09:39 ctl_ORCL_20150409_66_1.bak
-rw-r-----. 1 oracle asmadmin 75538432 Apr 9 09:38 full_ORCLxxxx_20150409_876562667_61_1.bak
-rw-r-----. 1 oracle asmadmin 211828736 Apr 9 09:38 full_ORCLxxxx_20150409_876562667_62_1.bak
-rw-r-----. 1 oracle asmadmin 98304 Apr 9 09:38 spfile_ORCLxxxx_26q3ujt4_1_1_20150409.bak
[root@rhel6 orcl]#
[root@rhel6 orcl]# su - oracle
[oracle@rhel6 ~]$ cd /home/oracle/oracle_bk/
[oracle@rhel6 oracle_bk]$ scp -r orcl oracle@192.168.59.10:/tmp/
oracle@192.168.59.10's password:
full_ORCLxxxx_20150409_876562667_62_1.bak 100% 202MB 10.6MB/s 00:19
arch_ORCL_20150409_65_1.bak 100% 2560 2.5KB/s 00:00
ctl_ORCL_20150409_66_1.bak 100% 1088KB 1.1MB/s 00:00
arch_ORCL_20150409_64_1.bak 100% 3072 3.0KB/s 00:00
full_ORCLxxxx_20150409_876562667_61_1.bak 100% 72MB 72.0MB/s 00:01
spfile_ORCLxxxx_26q3ujt4_1_1_20150409.bak 100% 96KB 96.0KB/s 00:00
[oracle@rhel6 oracle_bk]$
[oracle@rhel6 orcl]$ scp $ORACLE_HOME/dbs/initorcl.ora oracle@192.168.59.10:/tmp/orcl/
oracle@192.168.59.10's password:
initorcl.ora 100% 1035 1.0KB/s 00:00
[oracle@rhel6 orcl]$ scp $ORACLE_HOME/dbs/orapworcl oracle@192.168.59.10:/tmp/orcl/
oracle@192.168.59.10's password:
orapworcl 100% 1536 1.5KB/s 00:00
[oracle@rhel6 orcl]$
二、 在异机的操作
1、 恢复spfile
这里不采用rman恢复了，因为要实验异机不同路径的恢复，所以直接修改pfile文件吧。
[oracle@testdb orcl]$ cp initorcl.ora $ORACLE_HOME/dbs/
[oracle@testdb orcl]$ cp orapworcl $ORACLE_HOME/dbs/
[oracle@testdb orcl]$ vi $ORACLE_HOME/dbs/initorcl.ora
修改pfile文件之后：
[oracle@testdb orcl]$ more $ORACLE_HOME/dbs/initorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/orcltest/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcltest/control01.ctl','/u01/app/oracle/oradata/orcltest/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=8589934592
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=1000
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=314572800
*.open_cursors=300
*.processes=50
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=60
*.undo_tablespace='UNDOTBS1'
[oracle@testdb orcl]$
创建相关路径：
[oracle@testdb orcl]$ mkdir -p /u01/app/oracle/admin/orcltest/adump
[oracle@testdb orcl]$ mkdir -p /u01/app/oracle/oradata/orcltest/
[oracle@testdb orcl]$
注意： 这里其实根据后边的restore命令看还应该创建之前的数据文件路径(mkdir -p /u01/app/oracle/oradata/orcl)，不然报错:
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/goldengate01.dbf
channel c1: reading from backup piece /tmp/orcl/full_ORCLxxxx_20150409_876562667_61_1.bak
channel c1: ORA-19870: error while restoring backup piece /tmp/orcl/full_ORCLxxxx_20150409_876562667_61_1.bak
ORA-19504: failed to create file "/u01/app/oracle/oradata/orcl/sysaux01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel c1: reading from backup piece /tmp/orcl/full_ORCLxxxx_20150409_876562667_62_1.bak
channel c1: ORA-19870: error while restoring backup piece /tmp/orcl/full_ORCLxxxx_20150409_876562667_62_1.bak
ORA-19504: failed to create file "/u01/app/oracle/oradata/orcl/system01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
failover to previous backup
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/09/2015 11:58:21
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
[oracle@testdb orcl]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 9 10:31:00 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2227944 bytes
Variable Size 209715480 bytes
Database Buffers 96468992 bytes
Redo Buffers 4747264 bytes
SQL>
2、 恢复控制文件
[oracle@testdb orcl]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 9 10:32:27 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> restore controlfile to '/u01/app/oracle/oradata/orcltest/control01.ctl' from '/tmp/orcl/ctl_ORCL_20150409_66_1.bak';
Starting restore at 09-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-APR-15
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@testdb orcl]$ cp /u01/app/oracle/oradata/orcltest/control01.ctl /u01/app/oracle/oradata/orcltest/control02.ctl
[oracle@testdb orcl]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 9 10:37:15 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
3、 恢复归档文件
RMAN> catalog start with '/tmp/orcl/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/orcl/
List of Files Unknown to the Database
=====================================
File Name: /tmp/orcl/spfile_ORCLxxxx_26q3ujt4_1_1_20150409.bak
File Name: /tmp/orcl/full_ORCLxxxx_20150409_876562667_62_1.bak
File Name: /tmp/orcl/arch_ORCL_20150409_65_1.bak
File Name: /tmp/orcl/initorcl.ora
File Name: /tmp/orcl/orapworcl
File Name: /tmp/orcl/ctl_ORCL_20150409_66_1.bak
File Name: /tmp/orcl/arch_ORCL_20150409_64_1.bak
File Name: /tmp/orcl/full_ORCLxxxx_20150409_876562667_61_1.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/orcl/spfile_ORCLxxxx_26q3ujt4_1_1_20150409.bak
File Name: /tmp/orcl/full_ORCLxxxx_20150409_876562667_62_1.bak
File Name: /tmp/orcl/arch_ORCL_20150409_65_1.bak
File Name: /tmp/orcl/ctl_ORCL_20150409_66_1.bak
File Name: /tmp/orcl/arch_ORCL_20150409_64_1.bak
File Name: /tmp/orcl/full_ORCLxxxx_20150409_876562667_61_1.bak
List of Files Which Where Not Cataloged
=======================================
File Name: /tmp/orcl/initorcl.ora
RMAN-07517: Reason: The file header is corrupted
File Name: /tmp/orcl/orapworcl
RMAN-07517: Reason: The file header is corrupted
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
35 2.50K DISK 00:00:00 09-APR-15
BP Key: 41 Status: AVAILABLE Compressed: YES Tag: TAG20150409T093907
Piece Name: /tmp/orcl/arch_ORCL_20150409_64_1.bak
List of Archived Logs in backup set 35
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 1711260 09-APR-15 1711504 09-APR-15
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
36 2.00K DISK 00:00:00 09-APR-15
BP Key: 39 Status: AVAILABLE Compressed: YES Tag: TAG20150409T093907
Piece Name: /tmp/orcl/arch_ORCL_20150409_65_1.bak
List of Archived Logs in backup set 36
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 14 1711504 09-APR-15 1711512 09-APR-15
RMAN> restore archivelog sequence between 13 and 14;
Starting restore at 09-APR-15
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=13
channel ORA_DISK_1: reading from backup piece /tmp/orcl/arch_ORCL_20150409_64_1.bak
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=14
channel ORA_DISK_2: reading from backup piece /tmp/orcl/arch_ORCL_20150409_65_1.bak
channel ORA_DISK_1: piece handle=/tmp/orcl/arch_ORCL_20150409_64_1.bak tag=TAG20150409T093907
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: piece handle=/tmp/orcl/arch_ORCL_20150409_65_1.bak tag=TAG20150409T093907
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
Finished restore at 09-APR-15
RMAN>
4、 恢复数据文件
由于恢复路径不同，所以需要set newname。
set pagesize 200 linesize 200
select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'
from v$datafile a
union all
select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'
from v$tempfile a
union all
SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' ||
a.MEMBER || ''''' ";'
FROM v$logfile a;
SQL> set pagesize 200 linesize 200
SQL> select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'
2 from v$datafile a
3 union all
4 select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'
5 from v$tempfile a
6 union all
7 SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' ||
8 a.MEMBER || ''''' ";'
9 FROM v$logfile a;
'SETNEWNAMEFORDATAFILE'||A.FILE#||'TO"'||A.NAME||'";'
------------------------------------------------------------------------------------------------------------------------------------------------------------
set newname for datafile 1 to "/u01/app/oracle/oradata/orcl/system01.dbf";
set newname for datafile 2 to "/u01/app/oracle/oradata/orcl/sysaux01.dbf";
set newname for datafile 3 to "/u01/app/oracle/oradata/orcl/undotbs01.dbf";
set newname for datafile 4 to "/u01/app/oracle/oradata/orcl/users01.dbf";
set newname for datafile 5 to "/u01/app/oracle/oradata/orcl/example01.dbf";
set newname for datafile 6 to "/u01/app/oracle/oradata/orcl/goldengate01.dbf";
set newname for tempfile 1 to "/u01/app/oracle/oradata/orcl/temp01.dbf";
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo03.log'' to ''/u01/app/oracle/oradata/orcl/redo03.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo02.log'' to ''/u01/app/oracle/oradata/orcl/redo02.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo01.log'' to ''/u01/app/oracle/oradata/orcl/redo01.log'' ";
10 rows selected.
SQL>
启动数据库到mount状态：
RMAN> shutdown abort;
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 313159680 bytes
Fixed Size 2227944 bytes
Variable Size 213909784 bytes
Database Buffers 92274688 bytes
Redo Buffers 4747264 bytes
RMAN> RUN
2> {
3> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
set newname for datafile 1 to "/u01/app/oracle/oradata/orcl/system01.dbf";
5> set newname for datafile 2 to "/u01/app/oracle/oradata/orcl/sysaux01.dbf";
6> set newname for datafile 3 to "/u01/app/oracle/oradata/orcl/undotbs01.dbf";
7> set newname for datafile 4 to "/u01/app/oracle/oradata/orcl/users01.dbf";
8> set newname for datafile 5 to "/u01/app/oracle/oradata/orcl/example01.dbf";
9> set newname for datafile 6 to "/u01/app/oracle/oradata/orcl/goldengate01.dbf";
10> set newname for tempfile 1 to "/u01/app/oracle/oradata/orcl/temp01.dbf";
11>
12> SET UNTIL sequence 14 thread 1;
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }
released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: c1
channel c1: SID=59 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET until clause
Starting restore at 09-APR-15
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/goldengate01.dbf
channel c1: reading from backup piece /tmp/orcl/full_ORCLxxxx_20150409_876562667_61_1.bak
channel c1: ORA-19870: error while restoring backup piece /tmp/orcl/full_ORCLxxxx_20150409_876562667_61_1.bak
ORA-19504: failed to create file "/u01/app/oracle/oradata/orcl/sysaux01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel c1: reading from backup piece /tmp/orcl/full_ORCLxxxx_20150409_876562667_62_1.bak
channel c1: ORA-19870: error while restoring backup piece /tmp/orcl/full_ORCLxxxx_20150409_876562667_62_1.bak
ORA-19504: failed to create file "/u01/app/oracle/oradata/orcl/system01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
failover to previous backup
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/09/2015 11:58:21
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN>
创建路径：
[oracle@testdb orcltest]$ mkdir -p /u01/app/oracle/oradata/orcl/
继续恢复：
RMAN> RUN
2> {
3> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
4> set newname for datafile 1 to "/u01/app/oracle/oradata/orcltest/system01.dbf";
5> set newname for datafile 2 to "/u01/app/oracle/oradata/orcltest/sysaux01.dbf";
6> set newname for datafile 3 to "/u01/app/oracle/oradata/orcltest/undotbs01.dbf";
7> set newname for datafile 4 to "/u01/app/oracle/oradata/orcltest/users01.dbf";
8> set newname for datafile 5 to "/u01/app/oracle/oradata/orcltest/example01.dbf";
9> set newname for datafile 6 to "/u01/app/oracle/oradata/orcltest/goldengate01.dbf";
10> set newname for tempfile 1 to "/u01/app/oracle/oradata/orcltest/temp01.dbf";
11> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo03.log'' to ''/u01/app/oracle/oradata/orcltest/redo03.log'' ";
12> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo02.log'' to ''/u01/app/oracle/oradata/orcltest/redo02.log'' ";
13> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo01.log'' to ''/u01/app/oracle/oradata/orcltest/redo01.log'' ";
14>
15> SET UNTIL sequence 14 thread 1;
16> RESTORE DATABASE;
17> SWITCH DATAFILE ALL;
18> RECOVER DATABASE;
19> }
allocated channel: c1
channel c1: SID=59 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo03.log'' to ''/u01/app/oracle/oradata/orcltest/redo03.log''
sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo02.log'' to ''/u01/app/oracle/oradata/orcltest/redo02.log''
sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo01.log'' to ''/u01/app/oracle/oradata/orcltest/redo01.log''
executing command: SET until clause
Starting restore at 09-APR-15
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00002 to /u01/app/oracle/oradata/orcltest/sysaux01.dbf
channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/orcltest/undotbs01.dbf
channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/orcltest/goldengate01.dbf
channel c1: reading from backup piece /tmp/orcl/full_ORCLxxxx_20150409_876562667_61_1.bak
channel c1: piece handle=/tmp/orcl/full_ORCLxxxx_20150409_876562667_61_1.bak tag=TAG20150409T093747
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:35
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/orcltest/system01.dbf
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/orcltest/users01.dbf
channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/orcltest/example01.dbf
channel c1: reading from backup piece /tmp/orcl/full_ORCLxxxx_20150409_876562667_62_1.bak
channel c1: piece handle=/tmp/orcl/full_ORCLxxxx_20150409_876562667_62_1.bak tag=TAG20150409T093747
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:46
Finished restore at 09-APR-15
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=876571281 file name=/u01/app/oracle/oradata/orcltest/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=876571281 file name=/u01/app/oracle/oradata/orcltest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=876571281 file name=/u01/app/oracle/oradata/orcltest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=876571282 file name=/u01/app/oracle/oradata/orcltest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=876571282 file name=/u01/app/oracle/oradata/orcltest/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=14 STAMP=876571282 file name=/u01/app/oracle/oradata/orcltest/goldengate01.dbf
Starting recover at 09-APR-15
starting media recovery
archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_04_09/o1_mf_1_13_blct686c_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_04_09/o1_mf_1_13_blct686c_.arc thread=1 sequence=13
media recovery complete, elapsed time: 00:00:00
Finished recover at 09-APR-15
released channel: c1
RMAN>
告警日志：
Thu Apr 09 12:00:00 2015
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/orcl/redo03.log' to '/u01/app/oracle/oradata/orcltest/redo03.log'
Completed: ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/orcl/redo03.log' to '/u01/app/oracle/oradata/orcltest/redo03.log'
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/orcl/redo02.log' to '/u01/app/oracle/oradata/orcltest/redo02.log'
Completed: ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/orcl/redo02.log' to '/u01/app/oracle/oradata/orcltest/redo02.log'
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/orcl/redo01.log' to '/u01/app/oracle/oradata/orcltest/redo01.log'
Completed: ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/orcl/redo01.log' to '/u01/app/oracle/oradata/orcltest/redo01.log'
Thu Apr 09 12:00:02 2015
Full restore complete of datafile 6 to datafile copy /u01/app/oracle/oradata/orcltest/goldengate01.dbf. Elapsed time: 0:00:01
checkpoint is 1711453
Full restore complete of datafile 3 to datafile copy /u01/app/oracle/oradata/orcltest/undotbs01.dbf. Elapsed time: 0:00:08
checkpoint is 1711453
last deallocation scn is 1710310
Undo Optimization current scn is 1665335
Thu Apr 09 12:00:30 2015
Full restore complete of datafile 2 to datafile copy /u01/app/oracle/oradata/orcltest/sysaux01.dbf. Elapsed time: 0:00:28
checkpoint is 1711453
last deallocation scn is 1654207
Full restore complete of datafile 4 to datafile copy /u01/app/oracle/oradata/orcltest/users01.dbf. Elapsed time: 0:00:00
checkpoint is 1711454
Thu Apr 09 12:00:44 2015
Full restore complete of datafile 5 to datafile copy /u01/app/oracle/oradata/orcltest/example01.dbf. Elapsed time: 0:00:05
checkpoint is 1711454
last deallocation scn is 965277
Thu Apr 09 12:01:16 2015
Full restore complete of datafile 1 to datafile copy /u01/app/oracle/oradata/orcltest/system01.dbf. Elapsed time: 0:00:39
checkpoint is 1711454
last deallocation scn is 1016625
Undo Optimization current scn is 1665335
Thu Apr 09 12:01:22 2015
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10508.trc:
ORA-19625: error identifying file /u01/app/oracle/oradata/orcl/system01.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Switch of datafile 1 complete to datafile copy
checkpoint is 1711454
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10508.trc:
ORA-19625: error identifying file /u01/app/oracle/oradata/orcl/sysaux01.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Switch of datafile 2 complete to datafile copy
checkpoint is 1711453
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10508.trc:
ORA-19625: error identifying file /u01/app/oracle/oradata/orcl/undotbs01.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Switch of datafile 3 complete to datafile copy
checkpoint is 1711453
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10508.trc:
ORA-19625: error identifying file /u01/app/oracle/oradata/orcl/users01.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Switch of datafile 4 complete to datafile copy
checkpoint is 1711454
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10508.trc:
ORA-19625: error identifying file /u01/app/oracle/oradata/orcl/example01.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Switch of datafile 5 complete to datafile copy
checkpoint is 1711454
Thu Apr 09 12:01:22 2015
Signalling error 1152 for datafile 5!
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10508.trc:
ORA-19625: error identifying file /u01/app/oracle/oradata/orcl/goldengate01.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Switch of datafile 6 complete to datafile copy
checkpoint is 1711453
Signalling error 1152 for datafile 6!
Checker run found 2 new persistent data failures
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
started logmerger process
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
alter database recover logfile '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_04_09/o1_mf_1_13_blct686c_.arc'
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_04_09/o1_mf_1_13_blct686c_.arc
ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_04_09/o1_mf_1_13_blct686c_.arc'...
alter database recover cancel
Media Recovery Canceled
Completed: alter database recover cancel
5、 startup upgrade打开数据库
[oracle@testdb orcl]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 9 13:33:44 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 11035
Session ID: 59 Serial number: 29
SQL>
SQL> shutdown abort;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--因为相同平台的数据库软件版本不一样，所以需要upgrade选项打开。
--shutdown数据库用upgrade选项打开数据库：
[oracle@testdb orcl]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 9 13:36:01 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2227944 bytes
Variable Size 230687000 bytes
Database Buffers 75497472 bytes
Redo Buffers 4747264 bytes
Database mounted.
Database opened.
SQL>
告警日志：
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1404385113
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Thu Apr 09 13:36:29 2015
ALTER DATABASE OPEN MIGRATE
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 1, block 2, scn 1711508
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/orcltest/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 1, block 3, scn 1731510
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
LGWR: STARTING ARCH PROCESSES
Thu Apr 09 13:36:29 2015
ARC0 started with pid=20, OS id=11161
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thread 1 advanced to log sequence 2 (thread open)
Thu Apr 09 13:36:30 2015
ARC1 started with pid=23, OS id=11163
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/orcltest/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Thu Apr 09 13:36:30 2015
ARC2 started with pid=24, OS id=11165
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thu Apr 09 13:36:31 2015
ARC3 started with pid=25, OS id=11167
Archived Log entry 65 added for thread 1 sequence 1 ID 0x53b53413 dest 1:
[11151] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:47697934 end:47698304 diff:370 (3 seconds)
Dictionary check beginning
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_11114.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/orcl/temp01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_11114.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/orcl/temp01.dbf'
File 201 not verified due to error ORA-01157
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile /u01/app/oracle/oradata/orcl/temp01.dbf
Database Characterset is ZHS16GBK
Updating 11.2.0.1.0 NLS parameters in sys.props$
-- adding 11.2.0.3.0 NLS parameters.
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Stopping background process MMNL
Stopping background process MMON
Starting background process MMON
Starting background process MMNL
Thu Apr 09 13:36:35 2015
MMON started with pid=15, OS id=11169
Thu Apr 09 13:36:35 2015
MMNL started with pid=16, OS id=11171
ALTER SYSTEM enable restricted session;
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off.
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;
Resource Manager disabled during database migration: plan '' not set
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY;
Resource Manager disabled during database migration
replication_dependency_tracking turned off (no async multimaster replication found)
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN MIGRATE
Thu Apr 09 13:36:37 2015
Starting background process CJQ0
Thu Apr 09 13:36:37 2015
CJQ0 started with pid=26, OS id=11173
6、 执行升级脚本catupgrd.sql 并编译失效对象
SQL> SELECT d.owner, count(1)
2 FROM dba_objects d
3 where status = 'INVALID'
4 GROUP BY d.owner;
OWNER COUNT(1)
------------------------------ ----------
PUBLIC 396
CTXSYS 1
SYS 93
SQL>
$ORACLE_HOME\RDBMS\ADMIN\catupgrd.sql
--执行这个脚本。这个脚本调用catlog.sql和 catproc.sql来重建字典对象等，在执行完这个脚本之后，我们可以关闭数据库后，正常打开数据库：
spool /tmp/upgrade.log
set echo on
@$ORACLE_HOME/rdbms/admin/catupgrd.sql;
spool off
Shutdown immediate
执行之前可以把以下参数设置大点,否则可能导致升级脚本不能正常执行，如果脚本执行失败可以关闭数据库重新startup upgrade后再重新执行该脚本：
Thu Apr 09 14:40:46 2015
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_11151.trc (incident=2870):
ORA-04031: unable to allocate 4096 bytes of shared memory ("java pool","unknown object","JOXLE^5e8bb91c",":SGAClass")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_11151.trc (incident=2871):
ORA-04031: unable to allocate 4096 bytes of shared memory ("java pool","unknown object","JOXLE^5e8bb91c",":SGAClass")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
catupgrd.sql 该脚本花费时间较长，大约30分钟，执行完毕后干净的关库后再重新打开数据库再检查是否还有失效的对象：
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql;
。。。。。。。。。。。省略
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above sql script is the final step of the upgrade. Please
DOC> review any errors in the spool log file. If there are any errors in
DOC> the spool file, consult the Oracle Database Upgrade Guide for
DOC> troubleshooting recommendations.
DOC>
DOC> Next restart for normal operation, and then run utlrp.sql to
DOC> recompile any invalid application objects.
DOC>
DOC> If the source database had an older time zone version prior to
DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC> with Oracle.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM This forces user to start a new sqlplus session in order
SQL> REM to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@testdb dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 9 15:14:26 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 501059584 bytes
Fixed Size 2229744 bytes
Variable Size 444598800 bytes
Database Buffers 46137344 bytes
Redo Buffers 8093696 bytes
Database mounted.
Database opened.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
5930
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2015-04-09 15:16:49
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>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2015-04-09 15:22:21
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
-------------------
0
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
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL>
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 04-09-2015 15:31:19
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.3.0 00:07:47
JServer JAVA Virtual Machine
. VALID 11.2.0.3.0 00:02:14
Oracle Workspace Manager
. VALID 11.2.0.3.0 00:00:33
OLAP Analytic Workspace
. VALID 11.2.0.3.0 00:00:00
OLAP Catalog
. VALID 11.2.0.3.0 00:00:00
Oracle OLAP API
. VALID 11.2.0.3.0 00:00:27
Oracle XDK
. VALID 11.2.0.3.0 00:00:00
Oracle Text
. VALID 11.2.0.3.0 00:00:00
Oracle XML Database
. VALID 11.2.0.3.0 00:00:00
Oracle Database Java Packages
. VALID 11.2.0.3.0 00:00:12
Oracle Multimedia
. VALID 11.2.0.3.0 00:02:28
Spatial
. VALID 11.2.0.3.0 00:02:11
Oracle Expression Filter
. VALID 11.2.0.3.0 00:00:10
Oracle Rules Manager
. VALID 11.2.0.3.0 00:00:09
Oracle Application Express
. VALID 3.2.1.00.10
Gathering Statistics
. 00:01:59
Total Upgrade Time: 00:18:15
PL/SQL procedure successfully completed.
SQL>
根据脚本提示，我们可以在重新编译的过程中，重开一个窗口执行SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); 来判断未编译的对象数量。
最后查询INVALID的对象消失，说明还原成功，剩下的就是其它一些tns及监听的配置等等后续操作，这里就不演示了。
...........................................................................................................................................................................................
本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1561185/
QQ：642808185 注明：ITPUB的文章标题
...........................................................................................................................................................................................
