Oracle 11g RMAN 異機恢復

feelpurple發表於2016-02-25
Oracle

源庫：兩節點RAC
目標庫：單例項資料庫

1、複製備份檔案到恢復測試伺服器

包括2016年2月17日的0級備份和歸檔日誌備份，2月18日的1級累積增量備份和歸檔日誌備份、2月19日的歸檔日誌備份。

scp /orabak/1bqu774c_1_1 /orabak/1cqu776a_1_1 root@10.100.40.34:/oradata/bk/rman
.....

複製控制檔案到恢復測試伺服器。

RMAN> LIST BACKUP OF CONTROLFILE;

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    

------- ---- -- ---------- ----------- ------------ -------------------

2048    Full    27.58M     DISK        00:00:06     2016-02-17 03:53:03

        BP Key: 2066   Status: AVAILABLE  Compressed: NO  Tag: TAG20160217T035257

        Piece Name: /orabak/c-1864798816-20160217-00

  Control File Included: Ckp SCN: 10667216583   Ckp time: 2016-02-17 03:52:57

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    

------- ---- -- ---------- ----------- ------------ -------------------

2052    Full    27.58M     DISK        00:00:01     2016-02-17 03:55:56

        BP Key: 2070   Status: AVAILABLE  Compressed: NO  Tag: TAG20160217T035555

        Piece Name: /orabak/c-1864798816-20160217-01

  Control File Included: Ckp SCN: 10667223419   Ckp time: 2016-02-17 03:55:55

 

scp /orabak/c-1864798816-20160217-00 root@10.100.40.34:/oradata/bk/rman


2、恢復引數檔案和控制檔案

[oracle@backup-recovery rman]$ rman nocatalog

 

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Feb 24 15:34:12 2016

 

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

 

RMAN> connect target /

 

connected to target database (not started)

 

RMAN> set dbid 1864798816;

 

executing command: SET DBID

 

RMAN> startup nomount

 

Oracle instance started

 

Total System Global Area   17103163392 bytes

 

Fixed Size                     2245480 bytes

Variable Size               2181041304 bytes

Database Buffers           14898167808 bytes

Redo Buffers                  21708800 bytes

 

--由於傳輸檔案使用的是root

[root@backup-recovery bk]#su -

[root@backup-recovery bk]# chown -R oracle.oinstall rman/

 

RMAN> RUN

{

  ALLOCATE CHANNEL c1 DEVICE TYPE disk;

  SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bk/rman/%F';

  RESTORE SPFILE 

    TO PFILE '/u01/app/oracle/product/11.2.0/db_1/dbs/initaftdb.ora' 

    FROM AUTOBACKUP  MAXDAYS 30;

  SHUT2> 3> 4> 5> 6> 7> 8> DOWN ABORT;

}9> 

 

allocated channel: c1

channel c1: SID=1072 device type=DISK

 

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

 

Starting restore at 24-FEB-16

 

channel c1: looking for AUTOBACKUP on day: 20160224

channel c1: looking for AUTOBACKUP on day: 20160223

channel c1: looking for AUTOBACKUP on day: 20160222

channel c1: looking for AUTOBACKUP on day: 20160221

channel c1: looking for AUTOBACKUP on day: 20160220

channel c1: looking for AUTOBACKUP on day: 20160219

channel c1: looking for AUTOBACKUP on day: 20160218

channel c1: looking for AUTOBACKUP on day: 20160217

channel c1: AUTOBACKUP found: /oradata/bk/rman/c-1864798816-20160217-00

channel c1: restoring spfile from AUTOBACKUP /oradata/bk/rman/c-1864798816-20160217-00

channel c1: SPFILE restore from AUTOBACKUP complete

Finished restore at 24-FEB-16

 

Oracle instance shut down


編輯生成的引數檔案，修改對應的控制檔案目錄、audit_file_dest、db_recovery_file_dest、db_create_online_log_dest 等目錄，保證指定的目錄存在。

[oracle@backup-recovery dbs]$ vim initaftdb.ora

 

__db_cache_size=1542724608

__java_pool_size=67108864

__large_pool_size=67108864

__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

__pga_aggregate_target=2375342080

__sga_target=19126026240

__shared_io_pool_size=0

__shared_pool_size=845104128

__streams_pool_size=67108864

*.audit_file_dest='/u01/app/oracle/admin/aftdb/adump'

*.audit_sys_operations=TRUE

*.audit_trail='db'

#*.cluster_database=true

*.compatible='11.2.0.0.0'

*.control_files='/oradata/aftdb/controlfile/control01.ctl','/oradata/aftdb/controlfile/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='/oradata/aftdb/data'

*.db_create_online_log_dest_1='/oradata/aftdb/log'

*.db_create_online_log_dest_2='/oradata/aftdb/log'

*.db_domain=''

*.db_name='aftdb'

*.db_recovery_file_dest='/oradata/aftdb/recovery'

*.db_recovery_file_dest_size=536759762944

*.db_unique_name='aftdb'

*.deferred_segment_creation=FALSE

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=aftdbXDB)'

*.fal_server='atfdbdg'

#aftdb2.instance_number=2

#aftdb1.instance_number=1

#*.log_archive_config='DG_CONFIG=(aftdb,atfdbdg)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/aftdb/arc'

#  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

#  DB_UNIQUE_NAME=aftdb'

#*.LOG_ARCHIVE_DEST_2='SERVICE=atfdbdg ASYNC

#  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

#DB_UNIQUE_NAME=atfdbdg

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.log_archive_dest_state_2='enable'

*.LOG_ARCHIVE_FORMAT='log%d_%t_%s_%r.arc'

*.LOG_ARCHIVE_MAX_PROCESSES=30

*.open_cursors=300

*.os_roles=FALSE

*.pga_aggregate_target=6357516288

*.processes=800

*.recyclebin='OFF'

#*.remote_listener='rac-scan:1521'

*.remote_login_passwordfile='EXCLUSIVE'

*.session_cached_cursors=200

*.sessions=885

*.sga_target=19072548864

*.sql92_security=TRUE

#*.STANDBY_FILE_MANAGEMENT='AUTO'

#aftdb2.thread=2

#aftdb1.thread=1

#aftdb.undo_tablespace='UNDOTBS3'

undo_tablespace='UNDOTBS1'

 --建立引數檔案中對應的目錄

mkdir -p /oradata/aftdb/data

mkdir -p /oradata/aftdb/log

mkdir -p /oradata/aftdb/arc

mkdir -p /oradata/aftdb/recovery

mkdir -p /oradata/aftdb/controlfile


用新還原的引數檔案啟動資料庫。

SQL> STARTUP FORCE NOMOUNT PFILE='/u01/app/oracle/product/11.2.0/db_1/dbs/initaftdb.ora';

ORACLE instance started.

 

Total System Global Area 1.9041E+10 bytes

Fixed Size     2236368 bytes

Variable Size  1543503920 bytes

Database Buffers  1.7448E+10 bytes

Redo Buffers    46587904 bytes

--執行控制檔案恢復

[oracle@backup-recovery dbs]$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 25 09:48:45 2016

 

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

 

connected to target database: AFTDB (not mounted)

 

RMAN> set dbid 1864798816;

 

executing command: SET DBID

 

RMAN>  RUN 

{

  ALLOCATE CHANNEL c1 DEVICE TYPE disk;

SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bk/rman/%F';

  RESTORE CONTROLFILE FROM AUTOBACKUP MAXDAYS 30;

  ALTER DATABASE MOUNT;

}2> 3> 4> 5> 6> 7> 

 using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=1174 device type=DISK

 

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

 

Starting restore at 25-FEB-16

 

recovery area destination: /oradata/aftdb/recovery

database name (or database unique name) used for search: AFTDB

channel c1: no AUTOBACKUPS found in the recovery area

channel c1: looking for AUTOBACKUP on day: 20160225

channel c1: looking for AUTOBACKUP on day: 20160224

channel c1: looking for AUTOBACKUP on day: 20160223

channel c1: looking for AUTOBACKUP on day: 20160222

channel c1: looking for AUTOBACKUP on day: 20160221

channel c1: looking for AUTOBACKUP on day: 20160220

channel c1: looking for AUTOBACKUP on day: 20160219

channel c1: looking for AUTOBACKUP on day: 20160218

channel c1: looking for AUTOBACKUP on day: 20160217

channel c1: AUTOBACKUP found: /oradata/bk/rman/c-1864798816-20160217-00

channel c1: restoring control file from AUTOBACKUP /oradata/bk/rman/c-1864798816-20160217-00

channel c1: control file restore from AUTOBACKUP complete

output file name=/oradata/aftdb/controlfile/control01.ctl

output file name=/oradata/aftdb/controlfile/control02.ctl

Finished restore at 25-FEB-16

 

database mounted

released channel: c1


在 RMAN 中登記傳輸過來的備份檔案。

 

RMAN> CATALOG START WITH '/oradata/bk/rman';

 

Starting implicit crosscheck backup at 25-FEB-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1174 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1 device type=DISK

Crosschecked 312 objects

Finished implicit crosscheck backup at 25-FEB-16

 

Starting implicit crosscheck copy at 25-FEB-16

using channel ORA_DISK_1

using channel ORA_DISK_2

Crosschecked 2 objects

Finished implicit crosscheck copy at 25-FEB-16

 

searching for all files in the recovery area

cataloging files...

no files cataloged

 

searching for all files that match the pattern /oradata/bk/rman

 

List of Files Unknown to the Database

=====================================

File Name: /oradata/bk/rman/11qu4c02_1_1

File Name: /oradata/bk/rman/18qu754j_1_1

File Name: /oradata/bk/rman/10qu4c02_1_1

File Name: /oradata/bk/rman/1iqua40d_1_1

File Name: /oradata/bk/rman/c-1864798816-20160217-00

File Name: /oradata/bk/rman/1hqua408_1_1

File Name: /oradata/bk/rman/17qu754j_1_1

File Name: /oradata/bk/rman/1jqua422_1_1

 

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

 

List of Cataloged Files

=======================

File Name: /oradata/bk/rman/11qu4c02_1_1

File Name: /oradata/bk/rman/18qu754j_1_1

File Name: /oradata/bk/rman/10qu4c02_1_1

File Name: /oradata/bk/rman/1iqua40d_1_1

File Name: /oradata/bk/rman/c-1864798816-20160217-00

File Name: /oradata/bk/rman/1hqua408_1_1

File Name: /oradata/bk/rman/17qu754j_1_1

File Name: /oradata/bk/rman/1jqua422_1_1

 

--檢查資料備份，刪除過期的備份

crosscheck backup;

delete noprompt expired backup;


3、恢復資料檔案並開啟資料庫

--檢視歸檔日誌備份的 SCN，決定介質恢復的 SCN

list backup of archivelog all;

BS Key  Size       Device Type Elapsed Time Completion Time    

------- ---------- ----------- ------------ -------------------

2052    6.15G      DISK        00:00:00     2016-02-19 07:52:34

        BP Key: 2072   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_AFTDB_00184_021916071802

        Piece Name: /oradata/bk/rman/1jqua422_1_1

 

  List of Archived Logs in backup set 2052

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

 

  2    12266   10713786121 2016-02-18 23:34:30 10731754131 2016-02-19 00:07:10

  2    12267   10731754131 2016-02-19 00:07:10 10731849681 2016-02-19 00:14:25

  2    12268   10731849681 2016-02-19 00:14:25 10732092124 2016-02-19 00:33:38

  2    12269   10732092124 2016-02-19 00:33:38 10732405415 2016-02-19 01:03:15

  2    12270   10732405415 2016-02-19 01:03:15 10732726304 2016-02-19 01:40:52

  2    12271   10732726304 2016-02-19 01:40:52 10734774257 2016-02-19 02:36:30

  2    12272   10734774257 2016-02-19 02:36:30 10735123263 2016-02-19 03:52:15

  2    12273   10735123263 2016-02-19 03:52:15 10735485192 2016-02-19 05:53:10

  2    12274   10735485192 2016-02-19 05:53:10 10735843618 2016-02-19 07:22:16

  2    12275   10735843618 2016-02-19 07:22:16 10736032424 2016-02-19 07:51:17

 

--執行 RESTORE 命令，恢復資料檔案

--在源庫生成 SET NEWNAME 的批次指令碼

 SQL> select 'SET NEWNAME FOR DATAFILE ' || file_id || ' TO ' ||
       '''/oradata/misdb/df' || file_id || '.dbf'''||';'
  from dba_data_files
 order by file_id; 


'SETNEWNAMEFORDATAFILE'||FILE_ID||'TO'||'''/ORADATA/AFTDB/DATA/DF'||FILE_ID||'.DBF;'''
------------------------------------------------------------------------------------------------------------------------------------------
SET NEWNAME FOR DATAFILE 1 TO '/oradata/aftdb/data/df1.dbf;'
SET NEWNAME FOR DATAFILE 2 TO '/oradata/aftdb/data/df2.dbf;'
SET NEWNAME FOR DATAFILE 3 TO '/oradata/aftdb/data/df3.dbf;'
SET NEWNAME FOR DATAFILE 4 TO '/oradata/aftdb/data/df4.dbf;'
SET NEWNAME FOR DATAFILE 5 TO '/oradata/aftdb/data/df5.dbf;'
SET NEWNAME FOR DATAFILE 6 TO '/oradata/aftdb/data/df6.dbf;'
SET NEWNAME FOR DATAFILE 7 TO '/oradata/aftdb/data/df7.dbf;'
SET NEWNAME FOR DATAFILE 8 TO '/oradata/aftdb/data/df8.dbf;'
SET NEWNAME FOR DATAFILE 9 TO '/oradata/aftdb/data/df9.dbf;'
SET NEWNAME FOR DATAFILE 10 TO '/oradata/aftdb/data/df10.dbf;'
SET NEWNAME FOR DATAFILE 11 TO '/oradata/aftdb/data/df11.dbf;'

.....


--編寫 SHELL 指令碼

[oracle@backup-recovery script]$ vim rman.sh 

echo "The operation starts .." >> /home/oracle/rman_timetable.log
echo `date` >> /home/oracle/rman_timetable.log
rman target / log=/home/oracle/rman.log << EOF
RUN
{
SET NEWNAME FOR DATAFILE 1 TO '/oradata/aftdb/data/df1.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/oradata/aftdb/data/df2.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/oradata/aftdb/data/df3.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/oradata/aftdb/data/df4.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/oradata/aftdb/data/df5.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/oradata/aftdb/data/df6.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/oradata/aftdb/data/df7.dbf';
SET NEWNAME FOR DATAFILE 8 TO '/oradata/aftdb/data/df8.dbf';
SET NEWNAME FOR DATAFILE 9 TO '/oradata/aftdb/data/df9.dbf';
SET NEWNAME FOR DATAFILE 10 TO '/oradata/aftdb/data/df10.dbf';
SET NEWNAME FOR DATAFILE 11 TO '/oradata/aftdb/data/df11.dbf';
SET NEWNAME FOR DATAFILE 12 TO '/oradata/aftdb/data/df12.dbf';
SET NEWNAME FOR DATAFILE 13 TO '/oradata/aftdb/data/df13.dbf';
SET NEWNAME FOR DATAFILE 14 TO '/oradata/aftdb/data/df14.dbf';
SET NEWNAME FOR DATAFILE 15 TO '/oradata/aftdb/data/df15.dbf';
SET NEWNAME FOR DATAFILE 16 TO '/oradata/aftdb/data/df16.dbf';
SET NEWNAME FOR DATAFILE 17 TO '/oradata/aftdb/data/df17.dbf';
SET NEWNAME FOR DATAFILE 18 TO '/oradata/aftdb/data/df18.dbf';
SET NEWNAME FOR DATAFILE 19 TO '/oradata/aftdb/data/df19.dbf';
SET NEWNAME FOR DATAFILE 20 TO '/oradata/aftdb/data/df20.dbf';
SET NEWNAME FOR DATAFILE 21 TO '/oradata/aftdb/data/df21.dbf';
SET NEWNAME FOR DATAFILE 22 TO '/oradata/aftdb/data/df22.dbf';
SET NEWNAME FOR DATAFILE 23 TO '/oradata/aftdb/data/df23.dbf';
SET NEWNAME FOR DATAFILE 24 TO '/oradata/aftdb/data/df24.dbf';
SET NEWNAME FOR DATAFILE 114 TO '/oradata/aftdb/data/df114.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}
exit
EOF
echo "The operation finished .." >> /home/oracle/rman_timetable.log
echo `date` >> /home/oracle/rman_timetable.log

--也可以直接使用下面命令，而不用為每個資料檔案命名

SET NEWNAME FOR DATABASE TO '/oradata1/%b';

nohup sh rma.sh &

tailf /home/oracle/rman.log


--恢復完成後，開啟資料庫

SQL> RECOVER DATABASE using backup controlfile UNTIL CHANGE 10736032424;
SQL> alter database open resetlogs;

 
Database altered.

--建立SPFILE

SQL> create spfile from PFILE='/u01/app/oracle/product/11.2.0/db_1/dbs/initaftdb.ora';





來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-1994603/，如需轉載，請註明出處，否則將追究法律責任。

相關文章