【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

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

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

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

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

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 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

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

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

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> 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>#

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

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> @$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的文章標題

...........................................................................................................................................................................................

【RMAN】RMAN跨版本恢復(上)

相關文章