背景:只有rman备份出来的数据文件和控制文件,归档备份全部丢失。
1、原库DBID
connected to target database: testdt (DBID=138268371)
RMAN 异机恢复的时候,db_name必须相同。 如果说要想改成其他的实例名,可以在恢复成功后,用nid命令修改。 实例名的信息会记录到
控制文件里,所以如果在恢复的时候,如果实例名不一致,恢复的时候会报错。
源环境的备份路径是/u01/backup/prod/data_bak
测试环境存放备份文件的路径是/rman_bak
/u01/app/oracle/data
2、测试环境
mkdir -p /u01/app/oracle/admin/testdt/adump
mkdir -p /u01/app/oracle/data/testdt
mkdir -p /u01/archivelog/prod
chown -R oracle:oinstall /u01/archivelog
提前做一个pfile
vi inittestdt.ora
testdt.__data_transfer_cache_size=0
testdt.__db_cache_size=3070230528
testdt.__inmemory_ext_roarea=0
testdt.__inmemory_ext_rwarea=0
testdt.__java_pool_size=16777216
testdt.__large_pool_size=33554432
testdt.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
testdt.__pga_aggregate_target=1358954496
testdt.__sga_target=4076863488
testdt.__shared_io_pool_size=201326592
testdt.__shared_pool_size=738197504
testdt.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/testdt/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/data/testdt/control01.ctl','/u01/app/oracle/data/testdt/control02.ctl'
*.db_block_size=8192
*.db_name='testdt'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdtXDB)'
*.log_archive_dest_1='location=/u01/archivelog/prod'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1292m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3874m
*.undo_tablespace='UNDOTBS1'
export ORACLE_SID=testdt
rman target /
RMAN> startup nomount;
Oracle instance started
Total System Global Area 4076863488 bytes
Fixed Size 8628304 bytes
Variable Size 922748848 bytes
Database Buffers 3137339392 bytes
Redo Buffers 8146944 bytes
RMAN> restore controlfile from '/rman_bak/testdt_con_20220719_testdt_6.ctl';
Starting restore at 22-JUL-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/data/testdt/control01.ctl
output file name=/u01/app/oracle/data/testdt/control02.ctl
Finished restore at 22-JUL-22
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 541.66M DISK 00:00:49 10-MAY-22
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20220510T170349
Piece Name: /u01/app/oracle/product/12.2.0/dbs/030t5orl_1_1
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt/system01.dbf
2 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt_his_data.dbf
3 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt/sysaux01.dbf
4 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt/undotbs01.dbf
5 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt_data.dbf
7 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 17.75G DISK 00:22:13 19-JUL-22
BP Key: 5 Status: AVAILABLE Compressed: YES Tag: TAG20220719T100720
Piece Name: /u01/backup/prod/data_bak/testdt_data_20220719_testdt_5_1.data
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 234189095 19-JUL-22 NO /u01/app/oracle/data/testdt/system01.dbf
2 Full 234189095 19-JUL-22 234208555 NO /u01/app/oracle/data/testdt_his_data.dbf
3 Full 234189095 19-JUL-22 NO /u01/app/oracle/data/testdt/sysaux01.dbf
4 Full 234189095 19-JUL-22 234267006 NO /u01/app/oracle/data/testdt/undotbs01.dbf
5 Full 234189095 19-JUL-22 234262879 NO /u01/app/oracle/data/testdt_data.dbf
7 Full 234189095 19-JUL-22 NO /u01/app/oracle/data/testdt/users01.dbf
RMAN> catalog start with '/rman_bak'; --指定新的备份集存放位置
searching for all files that match the pattern /rman_bak
List of Files Unknown to the Database
=====================================
File Name: /rman_bak/testdt_data_20220719_testdt_5_1.data
File Name: /rman_bak/testdt_con_20220719_testdt_6.ctl
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /rman_bak/testdt_data_20220719_testdt_5_1.data
File Name: /rman_bak/testdt_con_20220719_testdt_6.ctl
-----如果是路径不一致要用下面的脚本指定新路径---------------
vi restore.sh
rman nocatalog target / <<EOF
run
{
set newname for datafile 1 to "/app/u01/oradata/DTw2/system01.dbf";
set newname for datafile 2 to "/app/u01/oradata/DTw2/sysaux01.dbf";
set newname for datafile 3 to "/app/u01/oradata/DTw2/undotbs01.dbf";
set newname for datafile 4 to "/app/u01/oradata/DTw2/users01.dbf";
set newname for datafile 5 to "/app/u01/oradata/DTw2/example01.dbf";
restore database;
switch datafile all;
}
exit
EOF
-----如果是路径相同直接恢复---------------
RMAN> restore database;
Starting restore at 22-JUL-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/data/testdt/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/data/testdt_his_data.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/data/testdt/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/data/testdt/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/data/testdt_data.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/data/testdt/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/prod/data_bak/testdt_data_20220719_testdt_5_1.data
channel ORA_DISK_1: errors found reading piece handle=/u01/backup/prod/data_bak/testdt_data_20220719_testdt_5_1.data
channel ORA_DISK_1: failover to piece handle=/rman_bak/testdt_data_20220719_testdt_5_1.data tag=TAG20220719T100720
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:31:36
Finished restore at 22-JUL-22
RMAN> recover database;
Starting recover at 22-JUL-22
using channel ORA_DISK_1
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/22/2022 01:46:04
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 28293 and starting SCN of 234263405 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 28292 and starting SCN of 234255131 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 28291 and starting SCN of 234246397 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 28290 and starting SCN of 234237313 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 28289 and starting SCN of 234228911 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 28288 and starting SCN of 234220788 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 28287 and starting SCN of 234213744 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 28286 and starting SCN of 234205219 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 28285 and starting SCN of 234196736 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 28284 and starting SCN of 234189079 found to restore
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 541.66M DISK 00:00:49 10-MAY-22
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20220510T170349
Piece Name: /u01/app/oracle/product/12.2.0/dbs/030t5orl_1_1
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt/system01.dbf
2 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt_his_data.dbf
3 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt/sysaux01.dbf
4 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt/undotbs01.dbf
5 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt_data.dbf
7 Full 2837389 10-MAY-22 NO /u01/app/oracle/data/testdt/users01.dbf
BS Key Type LV Size
------- ---- -- ----------
5 Full 17.75G
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 234189095 19-JUL-22 NO /u01/app/oracle/data/testdt/system01.dbf
2 Full 234189095 19-JUL-22 234208555 NO /u01/app/oracle/data/testdt_his_data.dbf
3 Full 234189095 19-JUL-22 NO /u01/app/oracle/data/testdt/sysaux01.dbf
4 Full 234189095 19-JUL-22 234267006 NO /u01/app/oracle/data/testdt/undotbs01.dbf
5 Full 234189095 19-JUL-22 234262879 NO /u01/app/oracle/data/testdt_data.dbf
7 Full 234189095 19-JUL-22 NO /u01/app/oracle/data/testdt/users01.dbf
Backup Set Copy #1 of backup set 5
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:22:13 19-JUL-22 YES TAG20220719T100720
List of Backup Pieces for backup set 5 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
5 1 AVAILABLE /u01/backup/prod/data_bak/testdt_data_20220719_testdt_5_1.data
Backup Set Copy #2 of backup set 5
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:22:13 19-JUL-22 YES TAG20220719T100720
List of Backup Pieces for backup set 5 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
6 1 AVAILABLE /rman_bak/testdt_data_20220719_testdt_5_1.data
上面看到备份集的SCN是234189095,因为归档备份丢失,所以只能恢复到这个SCN
RMAN> recover database until scn 234189095;
Starting recover at 22-JUL-22
using channel ORA_DISK_1
starting media recovery
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/u01/app/oracle/data/testdt_his_data.dbf'
media recovery complete, elapsed time: 00:00:00
Finished recover at 22-JUL-22
RECOVER DATABASE backupset USING CONTROLFILE UNTIL CANCEL ;
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 07/22/2022 02:09:07
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/u01/app/oracle/data/testdt_his_data.dbf'
关闭数据库
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
在init.ora中加入如下参数
_allow_resetlogs_corruption=TRUE
系统recover到这个文件后找不到下一次SCN,就会报这个错,这是正常的。
如不想出现这个错误的话,则需要用recover database until scn 1117236 来不完全恢复。
SET UNTIL SCN 234189079;
recover database until scn 234189095;
用open resetlogs打开数据库
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
ORA-01092: ORACLE instance terminated. Disconnection forced
RMAN-03002: failure of sql statement command at 07/22/2022 05:41:21
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Process ID: 24344
Session ID: 9 Serial number: 6740
ORACLE error from target database:
ORA-03114: not connected to ORACLE
这里直接数据库宕机了
从sqlplus进入启动数据库
[oracle@oracle12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 22 05:41:59 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 4076863488 bytes
Fixed Size 8628304 bytes
Variable Size 922748848 bytes
Database Buffers 3137339392 bytes
Redo Buffers 8146944 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [4194], [79], [26], [], [], [], [],
[], [], [], [], []
Process ID: 24520
Session ID: 9 Serial number: 46450
查找Ora-600报错:
https://blog.51cto.com/lhrbest/2698447
4193:表示undo和redo不一致(Arg [a] Undo record seq number,Arg [b] Redo record seq number );
4194:表示也是undo和redo不一致(Arg [a] Maximum Undo record number in Undo block,Arg [b] Undo record number from Redo block)
故障解决:
1、修改inittestdt.ora 里面参数
undo_management= MANUAL
undo_tablespace= SYSTEM
顺便注释
#_allow_resetlogs_corruption=true
2、OPEN数据库
SQL> startup
ORACLE instance started.
Total System Global Area 4076863488 bytes
Fixed Size 8628304 bytes
Variable Size 922748848 bytes
Database Buffers 3137339392 bytes
Redo Buffers 8146944 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
SQL> shutdown immediate;
SQL> startup;
4、删除当前undo空间,重建新的undo空间
TABLESPACE_NAME TS_SIZE FREE_SPACE USAGE_SPACE USAGE_RATE
-------------------- ---------- ---------- ----------- ----------
TESTDT_HIS_DATA 7200 241.625 6958.375 96.64
SYSAUX 2550 562.0625 1987.9375 77.96
UNDOTBS1 32767.9844 34.125 32733.8594 99.9
USERS 5 4 1 20
TESTDT_DATA 25000 499.75 24500.25 98
SYSTEM 950 4.375 945.625 99.54
删除旧undo
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU48_3977065699$' found, terminate dropping tablespace
添加隐藏参数:
*._offline_rollback_segments=('_SYSSMU48_3977065699$')
shutdown immediate
startup
drop tablespace undotbs1 including contents and datafiles;
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
新建undo
create undo tablespace undotbs1 datafile '/u01/app/oracle/data/testdt/undotbs01.dbf' size 5G autoextend off;
SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/data/testdt/undotbs01.dbf' size 5G autoextend off;
Tablespace created.
TABLESPACE_NAME TS_SIZE FREE_SPACE USAGE_SPACE USAGE_RATE
-------------------- ---------- ---------- ----------- ----------
TESTDT_HIS_DATA 7200 241.625 6958.375 96.64
SYSAUX 2550 566.375 1983.625 77.79
UNDOTBS1 5120 5117.75 2.25 .04
USERS 5 4 1 20
TESTDT_DATA 25000 499.75 24500.25 98
SYSTEM 950 4.375 945.625 99.54
UNDOTBS001 5120 5117.75 2.25 .04
3、修改pfile参数
undo_management= AUTO
undo_tablespace= UNDOTBS1
注释掉隐藏参数
#*._offline_rollback_segments=('_SYSSMU48_3977065699$')
4、重新启动数据库
shutdown immediate
startup
成功启动数据库,且启动过程中alert日志无报错
----------------分割线-------------------------
参考文章:
https://blog.51cto.com/lhrbest/2698447
https://blog.csdn.net/weixin_36488954/article/details/116447082