先恢复环境所需要的虚拟机
oracle数据库恢复步骤
确认需要恢复的时间点，找最新可用的备份信息
图形化查找
在备份平台上找到最新可用的控制文件备份
命令查找,在客户端或服务器上执行
C:\Program Files\Veritas\NetBackup\bin>bplist -C ycerpprd1.yc6k.com -S yc6k-nbu1 -t 4 -R / | more
cntrl_1767_1_1013077152:\
arch_s1766_p1_t1013077114:\
arch_s1765_p1_t1013077078:\
arch_s1764_p1_t1013077078:\
cntrl_1763_1_1013069952:\
arch_s1762_p1_t1013069914:\
arch_s1760_p1_t1013069878:\
arch_s1761_p1_t1013069878:\
cntrl_1759_1_1013062745:\
arch_s1758_p1_t1013062707:\
arch_s1757_p1_t1013062671:\
arch_s1756_p1_t1013062670:\
cntrl_1755_1_1013055546:\
arch_s1754_p1_t1013055509:\
arch_s1753_p1_t1013055472:\
cntrl_1767_1_1013077152
查找最近半天的archivelog备份
RMAN> list backup of archivelog from time 'sysdate - 0.5';
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
814 5.00M SBT_TAPE 00:00:27 08-JUL-19
BP Key: 814 Status: AVAILABLE Compressed: NO Tag: TAG20190708T011753
Handle: arch_s1746_p1_t1013044710 Media: @aaaae
List of Archived Logs in backup set 814
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78319 306889603827 08-JUL-19 306893345161 08-JUL-19
1 78320 306893345161 08-JUL-19 306893345167 08-JUL-19
1 78321 306893345167 08-JUL-19 306893345173 08-JUL-19
1 78322 306893345173 08-JUL-19 306893345200 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
816 256.00K SBT_TAPE 00:00:28 08-JUL-19
BP Key: 816 Status: AVAILABLE Compressed: NO Tag: TAG20190708T021750
Handle: arch_s1749_p1_t1013048271 Media: @aaaae
List of Archived Logs in backup set 816
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78326 306894407546 08-JUL-19 306894407556 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
817 25.50M SBT_TAPE 00:00:30 08-JUL-19
BP Key: 817 Status: AVAILABLE Compressed: NO Tag: TAG20190708T021750
Handle: arch_s1748_p1_t1013048271 Media: @aaaae
List of Archived Logs in backup set 817
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78323 306893345200 08-JUL-19 306894407534 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
818 256.00K SBT_TAPE 00:00:25 08-JUL-19
BP Key: 818 Status: AVAILABLE Compressed: NO Tag: TAG20190708T021750
Handle: arch_s1750_p1_t1013048308 Media: @aaaae
List of Archived Logs in backup set 818
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78324 306894407534 08-JUL-19 306894407540 08-JUL-19
1 78325 306894407540 08-JUL-19 306894407546 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
820 256.00K SBT_TAPE 00:00:28 08-JUL-19
BP Key: 820 Status: AVAILABLE Compressed: NO Tag: TAG20190708T041751
Handle: arch_s1753_p1_t1013055472 Media: @aaaae
List of Archived Logs in backup set 820
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78330 306911731355 08-JUL-19 306911732863 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
821 40.00M SBT_TAPE 00:00:29 08-JUL-19
BP Key: 821 Status: AVAILABLE Compressed: NO Tag: TAG20190708T041751
Handle: arch_s1752_p1_t1013055472 Media: @aaaae
List of Archived Logs in backup set 821
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78327 306894407556 08-JUL-19 306911730749 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
822 256.00K SBT_TAPE 00:00:26 08-JUL-19
BP Key: 822 Status: AVAILABLE Compressed: NO Tag: TAG20190708T041751
Handle: arch_s1754_p1_t1013055509 Media: @aaaae
List of Archived Logs in backup set 822
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78328 306911730749 08-JUL-19 306911731162 08-JUL-19
1 78329 306911731162 08-JUL-19 306911731355 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
824 9.50M SBT_TAPE 00:00:28 08-JUL-19
BP Key: 824 Status: AVAILABLE Compressed: NO Tag: TAG20190708T061749
Handle: arch_s1756_p1_t1013062670 Media: @aaaae
List of Archived Logs in backup set 824
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78331 306911732863 08-JUL-19 306911778476 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
825 256.00K SBT_TAPE 00:00:29 08-JUL-19
BP Key: 825 Status: AVAILABLE Compressed: NO Tag: TAG20190708T061749
Handle: arch_s1757_p1_t1013062671 Media: @aaaae
List of Archived Logs in backup set 825
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78334 306911778505 08-JUL-19 306911778514 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
826 256.00K SBT_TAPE 00:00:26 08-JUL-19
BP Key: 826 Status: AVAILABLE Compressed: NO Tag: TAG20190708T061749
Handle: arch_s1758_p1_t1013062707 Media: @aaaae
List of Archived Logs in backup set 826
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78332 306911778476 08-JUL-19 306911778482 08-JUL-19
1 78333 306911778482 08-JUL-19 306911778505 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
828 256.00K SBT_TAPE 00:00:28 08-JUL-19
BP Key: 828 Status: AVAILABLE Compressed: NO Tag: TAG20190708T081756
Handle: arch_s1761_p1_t1013069878 Media: @aaaae
List of Archived Logs in backup set 828
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78338 306927803632 08-JUL-19 306927803642 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
829 20.00M SBT_TAPE 00:00:29 08-JUL-19
BP Key: 829 Status: AVAILABLE Compressed: NO Tag: TAG20190708T081756
Handle: arch_s1760_p1_t1013069878 Media: @aaaae
List of Archived Logs in backup set 829
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78335 306911778514 08-JUL-19 306927803620 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
830 256.00K SBT_TAPE 00:00:26 08-JUL-19
BP Key: 830 Status: AVAILABLE Compressed: NO Tag: TAG20190708T081756
Handle: arch_s1762_p1_t1013069914 Media: @aaaae
List of Archived Logs in backup set 830
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78336 306927803620 08-JUL-19 306927803626 08-JUL-19
1 78337 306927803626 08-JUL-19 306927803632 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
832 46.75M SBT_TAPE 00:00:29 08-JUL-19
BP Key: 832 Status: AVAILABLE Compressed: NO Tag: TAG20190708T101757
Handle: arch_s1765_p1_t1013077078 Media: @aaaae
List of Archived Logs in backup set 832
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78340 306930200750 08-JUL-19 306931050305 08-JUL-19
1 78341 306931050305 08-JUL-19 306931050311 08-JUL-19
1 78342 306931050311 08-JUL-19 306931050317 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
833 49.50M SBT_TAPE 00:00:30 08-JUL-19
BP Key: 833 Status: AVAILABLE Compressed: NO Tag: TAG20190708T101757
Handle: arch_s1764_p1_t1013077078 Media: @aaaae
List of Archived Logs in backup set 833
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78339 306927803642 08-JUL-19 306930200750 08-JUL-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
834 256.00K SBT_TAPE 00:00:26 08-JUL-19
BP Key: 834 Status: AVAILABLE Compressed: NO Tag: TAG20190708T101757
Handle: arch_s1766_p1_t1013077114 Media: @aaaae
List of Archived Logs in backup set 834
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 78343 306931050317 08-JUL-19 306931050326 08-JUL-19
RMAN>
确认最新的 seq 为 78343 ，修复数据库的时候使用 recover database until logseq 78344;
编写数据库启动文件 pfile0707.ora ，内容如下
*.background_dump_dest='S:\oracle\PRD\saptrace\background'
*.compatible='10.2.0'
*.control_file_record_keep_time=30
*.control_files='D:\oracle\PRD\origlogA\cntrl\cntrlPRD.dbf','D:\oracle\PRD\origlogB\cntrl\cntrlPRD.dbf','S:\oracle\PRD\sapdata1\cntrl\cntrlPRD.dbf'
*.core_dump_dest='S:\oracle\PRD\saptrace\background'
*.db_block_size=8192
*.db_cache_size=1288490188
*.db_files=254
*.db_name='PRD'
*.dml_locks=4000
*.event='10191 trace name context forever, level 1'
*.FILESYSTEMIO_OPTIONS='setall'
*.job_queue_processes=1
*.log_archive_dest='T:\oracle\PRD\oraarch\PRDarch'
*.log_buffer=1048576
*.log_checkpoint_interval=0
*.log_checkpoints_to_alert=true
*.open_cursors=800
*.parallel_execution_message_size=16384
*.pga_aggregate_target=629145600
*.processes=80
*.query_rewrite_enabled='false'
*.recyclebin='off'
*.remote_login_passwordfile='exclusive'
*.remote_os_authent=true
*.replication_dependency_tracking=false
*.sessions=160
*.sga_max_size=2576980377
*.shared_pool_reserved_size=128849018
*.shared_pool_size=1288490188
*.sort_area_retained_size=0
*.sort_area_size=2097152
*.star_transformation_enabled='true'
*.undo_management='AUTO'
*.undo_retention=43200
*.undo_tablespace='PSAPUNDO'
*.user_dump_dest='S:\oracle\PRD\saptrace\usertrace'
以pfile0707.ora启动数据库到 nomount 状态
sqlplus / as sysdba
shutdown immediate
startup nomount pfile='D:\oracle\PRD\102\dbs\pfile0707.ora'
### 进入rman恢复工具，设置原库的DBID，生产机为 set DBID=1798771887
```shell
rman target / nocatalog
set DBID=1798771887
run {
allocate channel ch00 type 'SBT_TAPE';
send 'NB_ORA_SERV=yc6k-nbu1,NB_ORA_CLIENT=ycerpprd1.yc6k.com';
restore controlfile from 'cntrl_1767_1_1013077152';## 恢复最新的控制文件
}
控制文件恢复成功
控制文件恢复成功
启动数据库到 mount 状态
sql 'alter database mount';
恢复数据库
run {
allocate channel ch00 type 'SBT_TAPE' parms="ENV=(NB_ORA_SERV=yc6k-nbu1,NB_ORA_CLIENT=ycerpprd1.yc6k.com)";
allocate channel ch01 type 'SBT_TAPE' parms="ENV=(NB_ORA_SERV=yc6k-nbu1,NB_ORA_CLIENT=ycerpprd1.yc6k.com)";
send 'NB_ORA_SERV=yc6k-nbu1,NB_ORA_CLIENT=ycerpprd1.yc6k.com';
restore database;
}
restore数据库
恢复归档日志
run {
allocate channel ch00 type 'SBT_TAPE' parms="ENV=(NB_ORA_SERV=yc6k-nbu1,NB_ORA_CLIENT=ycerpprd1.yc6k.com)";
allocate channel ch01 type 'SBT_TAPE' parms="ENV=(NB_ORA_SERV=yc6k-nbu1,NB_ORA_CLIENT=ycerpprd1.yc6k.com)";
send 'NB_ORA_SERV=yc6k-nbu1,NB_ORA_CLIENT=ycerpprd1.yc6k.com';
restore archivelog from time 'sysdate - 0.5';
}
恢复归档日志文件
归档日志文件恢复完成
修复数据库 recover database
RMAN> recover database until logseq 78344;
Starting recover at 08-JUL-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-JUL-19
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/08/2019 12:54:51
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
database opened
RMAN>
成功打开数据库
修改数据库用户密码
C:\Users\prdadm>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 8 13:01:25 2019
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter user system identified by Yc6k2019;
User altered.
SQL> alter user sys identified by Yc6k2019;
User altered.
SQL> alter user sapsr3 identified by Yc6k2019;
User altered.
SQL>
rman恢复后，需要重建临时表空间
问题：
Oracle经过Rman恢复后未重建临时表空间导致查询dba_temp_files报错：
13:44:58 SQL> select tablespace_name,round(free_space/1024/1024/1024,2) "free(GB)",round(tablespace_size/1024/1024/1024,2) "total(GB)",round(nvl(free_space,0)*100/tablespace_size,3) "Free percent" from dba_temp_free_space;
select tablespace_name,round(free_space/1024/1024/1024,2) "free(GB)",round(tablespace_size/1024/1024/1024,2) "total(GB)",round(nvl(free_space,0)*100/tablespace_size,3) "Free percent" from dba_temp_free_space
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.56
13:45:11 SQL>
解决方法：
查看v$tempfile确定有几个temp临时表空间和对应的临时数据文件：
13:45:11 SQL> select file#,ts#,name,status from v$tempfile;
FILE# TS# NAME STATUS
1 3 S:\ORACLE\PRD\SAPDATA1\TEMP_1\TEMP.DATA1 ONLINE
Elapsed: 00:00:00.08
13:46:02 SQL>
临时表空间reuse：
13:46:02 SQL> alter database tempfile 'S:\ORACLE\PRD\SAPDATA1\TEMP_1\TEMP.DATA1' drop;
Database altered.
Elapsed: 00:00:00.15
13:48:21 SQL> alter tablespace temp add tempfile 'S:\ORACLE\PRD\SAPDATA1\TEMP_1\TEMP.DATA1' reuse;
alter tablespace temp add tempfile 'S:\ORACLE\PRD\SAPDATA1\TEMP_1\TEMP.DATA1' reuse
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist
Elapsed: 00:00:00.08
13:48:49 SQL> c/temp add/PSAPTEMP add/
1* alter tablespace PSAPTEMP add tempfile 'S:\ORACLE\PRD\SAPDATA1\TEMP_1\TEMP.DATA1' reuse
13:53:51 SQL> /
Tablespace altered.
Elapsed: 00:00:00.10
13:53:56 SQL>
13:54:08 SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME FILE_NAME
PSAPTEMP S:\ORACLE\PRD\SAPDATA1\TEMP_1\TEMP.DATA1
Elapsed: 00:00:00.08
13:55:55 SQL>
重新创建SAP使用的对应的操作系统帐号信息
生产机使用的是YC6K这个域的帐号进行SAP维护，恢复后的系统是独立的环境，需要修改SAP的帐号信息
recreate oracle user
"OPSYCSAPPRD99\SAPSERVICEPRD"
--创建帐号 "OPS$YCSAPPRD99\PRDADM"
CREATE USER "OPS$YCSAPPRD99\PRDADM"
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE PSAPTEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for "OPS$YCSAPPRD99\PRDADM"
GRANT CONNECT TO "OPS$YCSAPPRD99\PRDADM";
GRANT RESOURCE TO "OPS$YCSAPPRD99\PRDADM";
GRANT SAPDBA TO "OPS$YCSAPPRD99\PRDADM";
ALTER USER "OPS$YCSAPPRD99\PRDADM" DEFAULT ROLE ALL;
-- 2 System Privileges for "OPS$YCSAPPRD99\PRDADM"
GRANT CREATE SESSION TO "OPS$YCSAPPRD99\PRDADM";
GRANT UNLIMITED TABLESPACE TO "OPS$YCSAPPRD99\PRDADM";
--创建原来的表到新用户上
CREATE TABLE "OPS$YCSAPPRD99\PRDADM".SAPUSER as select * from "OPS$YC6K\PRDADM".SAPUSER;
--创建帐号"OPS$YCSAPPRD99\SAPSERVICEPRD"
CREATE USER "OPS$YCSAPPRD99\SAPSERVICEPRD"
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE PSAPTEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for "OPS$YCSAPPRD99\SAPSERVICEPRD"
GRANT CONNECT TO "OPS$YCSAPPRD99\SAPSERVICEPRD";
GRANT RESOURCE TO "OPS$YCSAPPRD99\SAPSERVICEPRD";
GRANT SAPDBA TO "OPS$YCSAPPRD99\SAPSERVICEPRD";
ALTER USER "OPS$YCSAPPRD99\SAPSERVICEPRD" DEFAULT ROLE ALL;
-- 1 System Privilege for "OPS$YCSAPPRD99\SAPSERVICEPRD"
GRANT UNLIMITED TABLESPACE TO "OPS$YCSAPPRD99\SAPSERVICEPRD";
-- 1 Object Privilege for "OPS$YCSAPPRD99\SAPSERVICEPRD"
--授权表权限
GRANT INSERT, SELECT, UPDATE ON "OPS$YCSAPPRD99\PRDADM".SAPUSER TO "OPS$YCSAPPRD99\SAPSERVICEPRD";
CREATE OR REPLACE SYNONYM "OPS$YCSAPPRD99\SAPSERVICEPRD".SAPUSER FOR "OPS$YCSAPPRD99\PRDADM".SAPUSER;
--删除YC6K的帐号
DROP USER "OPS$YC6K\SAPSERVICEPRD" CASCADE;
DROP USER "OPS$YC6K\PRDADM" CASCADE;
相关SQL执行截图
重新配置更新SAP应用连接数据库的连接帐号密码
brconnect -u system/Yc6k2019 -f chpass -o SAPSR3 -p Yc6k2019
C:\Users\prdadm>brconnect -u system/Yc6k2019 -f chpass -o SAPSR3 -p Yc6k2019
BR0801I BRCONNECT 7.00 (40)
BR0280I BRCONNECT time stamp: 2019-07-08 14.11.44
BR0828I Changing password for database user SAPSR3
BR0280I BRCONNECT time stamp: 2019-07-08 14.11.44
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRCONNECT:
c
BR0280I BRCONNECT time stamp: 2019-07-08 14.11.46
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
BR0280I BRCONNECT time stamp: 2019-07-08 14.11.46
BR0829I Password changed successfully in database for user SAPSR3
BR0830I Password changed successfully in table OPS$YCSAPPRD99\PRDADM.SAPUSER for user SAPSR3
BR0280I BRCONNECT time stamp: 2019-07-08 14.11.46
BR0802I BRCONNECT completed successfully
验证数据库连通性
R3trans -d
C:\Users\prdadm>R3trans -d
This is R3trans version 6.14 (release 701 - 26.01.09 - 12:46:00).
unicode enabled version
R3trans finished (0000).
C:\Users\prdadm>
配置更新SAP连接数据库帐号密码信息
启动SAP应用
启动sap应用
运行状态正常
使用SAP Gui登陆
配置服务器为刚恢复的服务器IP或DNS域名
