1.1 Oracle数据库恢复
目录
1 安装新的客户端
2 新建异机恢复文件
3 新建数据库实例
4 建立spfile文件
5 建立数据文件夹
6 启动数据库到nomount状态
7 列出备份信息
8 还原controlfile，并mount数据库
9 查找数据文件信息
10 数据文件恢复
11 归档日志恢复
12 联机日志路径修改
13 Recover并打开数据库
14 验证数据库
1.1.1 安装新的客户端
在备份服务器上添加hosts信息
10.110.10.74 backup-test
在客户端添加hosts信息
10.12.69.111 nbu-b
安装客户端
1.1.2 新建异机恢复文件
在备份服务器上建立支持异机恢复要求的文件。
备份服务器建立No.Restrictions空文件
UNIX platform: /usr/openv/netbackup/db/altnames/No.Restrictions
1.1.3 新建数据库实例
对于需要恢复的机器，需要新建立一个数据库实例进行数据恢复。
本次恢复针到的实例为test
1.1.4 建立spfile文件
原spfile导出文件为：
|
[root@backup-test testtest]# cat inittest1.ora.old test2.__db_cache_size=7918845952 test1.__db_cache_size=6643777536 test2.__java_pool_size=134217728 test1.__java_pool_size=134217728 test2.__large_pool_size=167772160 test1.__large_pool_size=167772160 test1.__oracle_base='/oracle/'#ORACLE_BASE set from environment test2.__oracle_base='/oracle/'#ORACLE_BASE set from environment test2.__pga_aggregate_target=6442450944 test1.__pga_aggregate_target=5637144576 test2.__sga_target=9663676416 test1.__sga_target=10468982784 test2.__shared_io_pool_size=0 test1.__shared_io_pool_size=0 test2.__shared_pool_size=1342177280 test1.__shared_pool_size=3422552064 test2.__streams_pool_size=0 test1.__streams_pool_size=0 *.audit_file_dest='/oracle/admin/test/adump' *.audit_trail='db' *.cluster_database=true *.compatible='11.2.0.4.0' *.control_files='+DATA_DG/test/controlfile/current.256.834077963','+DATA_DG/test/controlfile/current.275.834330051','+FLASH_DG/test/controlfile/current.278.834330403' *.db_block_size=8192 *.db_create_file_dest='+DATA_DG' *.db_domain='' *.db_name='test' *.diagnostic_dest='/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)' test1.instance_number=1 test2.instance_number=2 *.lock_sga=FALSE *.log_archive_dest_1='LOCATION=+FLASH_DG' *.log_archive_format='%t_%s_%r.dbf' *.memory_max_target=16106127360 *.memory_target=16106127360 *.open_cursors=500 *.processes=1000 *.remote_listener='test-scan:1521' *.remote_login_passwordfile='exclusive' *.session_cached_cursors=100 *.sessions=1105 test2.thread=2 test1.thread=1 *.undo_retention=7200 test1.undo_tablespace='UNDOTBS1' test2.undo_tablespace='UNDOTBS2' |
由于需要恢复到单机，删除RAC相关信息。 另外由于硬件资源的不同，删除需要恢复的资源配置。
新的配置文件为
|
[root@backup-test testtest]# cat inittest1.ora *.__oracle_base='/oradata/testtest/'#ORACLE_BASE set from environment *.__pga_aggregate_target=5637144576 *.__sga_target=10468982784 *.audit_file_dest='/oradata/testtest/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/oradata/testtest/controlfile/current.256.834077963' *.db_block_size=8192 *.db_create_file_dest='/oradata/testtest/' *.db_domain='' *.db_name='test' *.diagnostic_dest='/oradata/testtest/' *.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)' *.lock_sga=FALSE *.log_archive_dest_1='LOCATION=/oradata/testtest/archive/' *.log_archive_format='%t_%s_%r.dbf' *.open_cursors=500 *.processes=1000 *.remote_login_passwordfile='exclusive' *.session_cached_cursors=100 *.sessions=1105 *.undo_retention=7200 *.undo_tablespace='UNDOTBS1' |
1.1.5 建立数据文件夹
根据spfile建立相关文件
|
[root@backup-test testtest]# ll -a total 52 drwxrwxrwx 7 oracle oinstall 4096 Sep 25 16:26 . drwxrwxr-x 29 oracle oinstall 12288 Sep 28 10:03 .. drwxr-xr-x 2 oracle oinstall 4096 Sep 28 10:37 adump drwxr-xr-x 2 oracle oinstall 4096 Sep 25 16:02 archive drwxr-xr-x 2 oracle oinstall 4096 Sep 28 10:10 controlfile drwxrwxr-x 3 oracle oinstall 4096 Sep 25 16:20 diag -rwxrwxrwx 1 oracle oinstall 760 Sep 25 16:26 inittest1.ora -rwxr-xr-x 1 oracle oinstall 1578 Sep 25 15:45 inittest1.ora.old drwxrwxrwx 2 oracle oinstall 4096 Sep 25 12:04 testtest -rwxrwxrwx 1 oracle oinstall 6520 Sep 25 14:44 utlpwdmg.sql |
1.1.6 启动数据库到nomount状态
|
sqlplus /nolog idle> conn / as sysdba idle> startup nomount pfile='/oradata/testtest/inittest1.ora' idle> startup nomount pfile='/oradata/testtest/inittest1.ora' ORACLE instance started. Total System Global Area 396726272 bytes Fixed Size 2253504 bytes Variable Size 318770496 bytes Database Buffers 67108864 bytes Redo Buffers 8593408 bytes
|
1.1.7 列出备份信息
在备份服务器上列出相关备份信息
|
bplist -C test-db01 -s 09/27/2015 -t 4 -R -b -l / 。。。。。。。 |
1.1.8 还原controlfile，并mount数据库
RMAN还原
|
RMAN> RMAN>connect target / RMAN> run{ 2> allocate channel ch0 type 'sbt_tape'; 3> send 'NB_ORA_CLIENT=test-db01'; 4> send 'nb_ora_serv=nbu-b'; 5> restore controlfile from '/cntrl_3175_1_891580028'; 6> release channel ch0; 7> } using target database control file instead of recovery catalog allocated channel: ch0 channel ch0: SID=578 device type=SBT_TAPE channel ch0: Veritas NetBackup for Oracle - Release 7.6 (2014102721) sent command to channel: ch0 sent command to channel: ch0 Starting restore at 2015-09-28 10:10:36 channel ch0: restoring control file channel ch0: restore complete, elapsed time: 00:00:15 output file name=/oradata/testtest/controlfile/current.256.834077963 Finished restore at 2015-09-28 10:10:52 released channel: ch0 RMAN> alter database mount; database mounted |
1.1.9 查找数据文件信息
|
sqlplus /nolog idle> conn / as sysdba idle> select name from v$datafile; NAME ---------------------------------------------------------------------------------------------------- +DATA_DG/test/datafile/system.259.834077963 +DATA_DG/test/datafile/sysaux.260.834077967 +DATA_DG/test/datafile/undotbs1.261.834077971 +DATA_DG/test/datafile/undotbs2.263.834077979 +DATA_DG/test/datafile/users.264.834077981 +DATA_DG/test/datafile/zhos_dataspace.268.889894605 +DATA_DG/test/datafile/zhos_dataspace.272.834956161 +DATA_DG/test/datafile/zhos_dataspace.271.834956199 +DATA_DG/test/datafile/spcred_dataspace.269.834956241 +DATA_DG/test/datafile/zhos_indexspace.273.834317857 +DATA_DG/test/datafile/audit_dataspace.280.846839715 11 rows selected. idle> select member from v$logfile; MEMBER ---------------------------------------------------------------------------------------------------- +DATA_DG/test/onlinelog/group_1.257.834077963 +DATA_DG/test/onlinelog/group_2.258.834077963 +DATA_DG/test/onlinelog/group_3.265.834080437 +DATA_DG/test/onlinelog/group_4.266.834080437 +DATA_DG/test/onlinelog/group_5.276.834332677 +DATA_DG/test/onlinelog/group_6.277.834332723 +DATA_DG/test/onlinelog/group_7.278.834332751 +DATA_DG/test/onlinelog/group_8.279.834332783 8 rows selected.
|
1.1.10 数据文件恢复
编辑还原脚本如下：
|
export ORACLE_SID=test rman <<EOF connect target / run { allocate channel ch0 type 'SBT_TAPE'; allocate channel ch1 type 'SBT_TAPE'; send 'nb_ora_serv=nbu-b'; send 'nb_ora_client=test-db01'; set newname for datafile '+DATA_DG/test/datafile/system.259.834077963' to '/oradata/testtest/system.259.834077963'; set newname for datafile '+DATA_DG/test/datafile/sysaux.260.834077967' to '/oradata/testtest/sysaux.260.834077967'; set newname for datafile '+DATA_DG/test/datafile/undotbs1.261.834077971' to '/oradata/testtest/undotbs1.261.834077971'; set newname for datafile '+DATA_DG/test/datafile/undotbs2.263.834077979' to '/oradata/testtest/undotbs2.263.834077979'; set newname for datafile '+DATA_DG/test/datafile/users.264.834077981' to '/oradata/testtest/users.264.834077981'; set newname for datafile '+DATA_DG/test/datafile/zhos_dataspace.268.889894605' to '/oradata/testtest/zhos_dataspace.268.889894605'; set newname for datafile '+DATA_DG/test/datafile/zhos_dataspace.272.834956161' to '/oradata/testtest/zhos_dataspace.272.834956161'; set newname for datafile '+DATA_DG/test/datafile/zhos_dataspace.271.834956199' to '/oradata/testtest/zhos_dataspace.271.834956199'; set newname for datafile '+DATA_DG/test/datafile/spcred_dataspace.269.834956241' to '/oradata/testtest/spcred_dataspace.269.834956241'; set newname for datafile '+DATA_DG/test/datafile/zhos_indexspace.273.834317857' to '/oradata/testtest/zhos_indexspace.273.834317857'; set newname for datafile '+DATA_DG/test/datafile/audit_dataspace.280.846839715' to '/oradata/testtest/audit_dataspace.280.846839715'; restore database; switch datafile all; release channel ch0; release channel ch1; } exit EOF |
运行数据还原
恢复完成
1.1.11 归档日志恢复
|
idle> select THREAD#,SEQUENCE#,FIRST_TIME,COMPLETION_TIME from v$archived_log where COMPLETION_TIME>to_date('20150927 20:00:00','yyyymmdd hh24:mi:ss') and COMPLETION_TIME<to_date('20150928 6:00:00','yyyymmdd hh24:mi:ss') order by SEQUENCE#; THREAD# SEQUENCE# FIRST_TIME COMPLETION_TIME ---------- ---------- ------------------- ------------------- 1 27587 2015-09-27 19:59:31 2015-09-27 21:01:28 ...... 39 rows selected. |
RMAN还原归档日志
节点1还原
|
RMAN> connect target / connected to target database: test (DBID=825185095, not open) RMAN> run{ set archivelog destination to '/oradata/testtest/archive'; 2> 3> allocate channel ch0 type 'SBT_TAPE'; send 'NB_ORA_CLIENT=test-db01'; send 'nb_ora_serv=nbu-b'; restore archivelog sequence between 27587 and 27597 thread 1; 4> 5> 6> 7> release channel ch0; 8> } executing command: SET ARCHIVELOG DESTINATION ...... released channel: ch0 |
节点2还原
|
RMAN> run{ set archivelog destination to '/oradata/testtest/archive'; 2> 3> allocate channel ch0 type 'SBT_TAPE'; 4> send 'NB_ORA_CLIENT=test-db01'; 5> send 'nb_ora_serv=nbu-b'; 6> restore archivelog sequence between 45287 and 45314 thread 2; 7> release channel ch0; 8> } executing command: SET ARCHIVELOG DESTINATION allocated channel: ch0 channel ch0: SID=673 device type=SBT_TAPE channel ch0: Veritas NetBackup for Oracle - Release 7.6 (2014102721) sent command to channel: ch0 sent command to channel: ch0 Starting restore at 2015-09-28 14:14:04 channel ch0: starting archived log restore to user-specified destination ...... released channel: ch0 |
1.1.12 联机日志路径修改
Sqlplus 修改
|
idle> alter database rename file '+DATA_DG/test/onlinelog/group_1.257.834077963' to'/oradata/testtest/group_1.257.834077963'; ...... |
1.1.13 Recover并打开数据库
Sqlplus启动数据库
|
idle> recover database until cancel using backup controlfile; ORA-00279: change 10315737932005 generated at 09/27/2015 22:03:45 needed for thread 1 ORA-00289: suggestion : /oradata/testtest/archive/1_27589_834077959.dbf ORA-00280: change 10315737932005 for thread 1 is in sequence #27589 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO ORA-00308: cannot open archived log '/oradata/testtest/archive/1_27589_834077959.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 .............. ORA-00308: cannot open archived log '/oradata/testtest/archive/2_45315_834077959.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 idle> idle> alter database open resetlogs; Database altered. |
1.1.14 验证数据库
|
idle> select open_mode from v$database; OPEN_MODE ---------------------------------------- READ WRITE |