WinServer2008下Oracle10G异机还原

业务场景：因两台服务器遭到不同程度的硬件损坏，数据出现大量坏块。

重建数据数据库环境，用一个历史的Rman备份做异机还原。

本次重点：核心的业务逻辑都是用存储过程实现。不幸是归档日志没有打开，redo也遭到破坏

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>set ORACLE_SID=mes

C:\Users\Administrator>set nls_lang=american_america.zhs16gbk

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 5 21:57:17 2020

Connected to an idle instance.

SQL> startup nomount pfile='G:\ora_backup\pfile.ora';
ORA-02778: Name given for the log directory is invalid
此错误的原因是参数文件中对应的目录没有创建
SQL> startup nomount pfile='G:\ora_backup\pfile.ora';
LRM-00109: could not open parameter file 'G:\ora_backup\pfile.ora'
ORA-01078: failure in processing system parameters

此错误的原因是G:\ora_backup目录下没有pfile文件

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>set ORACLE_SID=mes

C:\Users\Administrator>set nls_lang=american_america.zhs16gbk

这个最后用以下三个系统环境变量解决：
ORACLE_SID=mes
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
TNS_ADMIN=D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

创建相应的目录：

C:\Users\Administrator>mkdir D:\oracle\product\10.2.0\admin\mes\adump
C:\Users\Administrator>mkdir D:\oracle\product\10.2.0\admin\mes\bdump
C:\Users\Administrator>mkdir D:\oracle\product\10.2.0\admin\mes\cdump
C:\Users\Administrator>mkdir D:\oracle\product\10.2.0\admin\mes\udump
C:\Users\Administrator>mkdir D:\oracle\product\10.2.0\flash_recovery_area
C:\Users\Administrator>mkdir G:\archivelog
C:\Users\Administrator>mkdir E:\CMES
C:\Users\Administrator>mkdir E:\RMES
C:\Users\Administrator>mkdir E:\HMES
C:\Users\Administrator>mkdir D:\oracle\product\10.2.0\oradata\mes
C:\Users\Administrator>mkdir F:\INDX

SQL> startup nomount pfile='F:\ora_backup\pfile.ora';
ORACLE instance started.

Total System Global Area 2.0972E+10 bytes
Fixed Size                  2103152 bytes
Variable Size            2365589648 bytes
Database Buffers         1.8589E+10 bytes
Redo Buffers               14671872 bytes
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITMES.ORA'

SQL> create spfile from pfile='F:\ora_backup\pfile.ora';

File created.

此时Oracle启动到了nomount状态下：

接下来启一个Rman窗口：

C:\Users\Administrator>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jun 5 22:08:50 2020

connected to target database: mes (not mounted)

RMAN> restore controlfile from 'F:\ora_backup\MES02V1JVJ0_1_1.BKP';

Starting restore at 05-JUN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=322 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\MES\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\MES\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\MES\CONTROL03.CTL
Finished restore at 05-JUN-20

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database preview ;

Starting restore at 05-JUN-20
Starting implicit crosscheck backup at 05-JUN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=321 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 05-JUN-20

Starting implicit crosscheck copy at 05-JUN-20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 05-JUN-20

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

data file 5 will be created automatically during restore operation
data file 6 will be created automatically during restore operation
data file 7 will be created automatically during restore operation
data file 8 will be created automatically during restore operation
data file 9 will be created automatically during restore operation
data file 10 will be created automatically during restore operation
data file 11 will be created automatically during restore operation
data file 12 will be created automatically during restore operation
data file 13 will be created automatically during restore operation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/05/2020 22:11:26
RMAN-06026: some targets not found - aborting 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> restore database;

Starting restore at 05-JUN-20
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/05/2020 22:12:20
RMAN-06026: some targets not found - aborting 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> catalog start with 'F:\ora_back'; 注册进控制文件后，Rman恢复喜新厌旧。

searching for all files that match the pattern F:\ora_back

List of Files Unknown to the Database
=====================================
File Name: F:\ora_back\MES01V1JV8G_1_1.BKP
File Name: F:\ora_back\MES02V1JVJ0_1_1.BKP
File Name: F:\ora_backup\CONTROL01.CTL
File Name: F:\ora_backup\CONTROL02.CTL
File Name: F:\ora_backup\CONTROL03.CTL
File Name: F:\ora_backup\MES01V1JV8G_1_1.BKP
File Name: F:\ora_backup\MES02V1JVJ0_1_1.BKP
File Name: F:\ora_backup\pfile.ora

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

List of Cataloged Files
=======================
File Name: F:\ora_back\MES01V1JV8G_1_1.BKP
File Name: F:\ora_back\MES02V1JVJ0_1_1.BKP
File Name: F:\ora_backup\CONTROL01.CTL
File Name: F:\ora_backup\CONTROL02.CTL
File Name: F:\ora_backup\CONTROL03.CTL
File Name: F:\ora_backup\MES01V1JV8G_1_1.BKP
File Name: F:\ora_backup\MES02V1JVJ0_1_1.BKP

List of Files Which Where Not Cataloged
=======================================
File Name: F:\ora_backup\pfile.ora
RMAN-07517: Reason: The file header is corrupted

RMAN> restore database;

Starting restore at 05-JUN-20
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\MES\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\MES\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\MES\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\MES\USERS01.DBF
restoring datafile 00005 to E:\CMES\CMES01.DBF
restoring datafile 00006 to E:\RMES\RMES01.DBF
restoring datafile 00007 to E:\RMES\RMES02.DBF
restoring datafile 00008 to F:\INDX\INDX01.DBF
restoring datafile 00009 to E:\HMES\HMES01.DBF
restoring datafile 00010 to E:\HMES\HMES02.DBF
restoring datafile 00011 to E:\RMES\RMES03.DBF
restoring datafile 00012 to F:\INDX\INDX02.DBF
restoring datafile 00013 to F:\INDX\INDX03.DBF
channel ORA_DISK_1: reading from backup piece F:\ORA_BACK\MES01V1JV8G_1_1.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=F:\ORA_BACK\MES01V1JV8G_1_1.BKP tag=TAG20200531T220256
channel ORA_DISK_1: restore complete, elapsed time: 00:30:09
Finished restore at 05-JUN-20

RMAN> alter database open;

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/05/2020 22:45:08
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\MES\SYSTEM01.DBF'

RMAN> recover database;

Starting recover at 05-JUN-20
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/05/2020 22:47:16
ORA-19698: D:\ORACLE\PRODUCT\10.2.0\ORADATA\MES\REDO01.LOG is from different dat
abase: id=2125883341, db_name=MES

RMAN> alter system set "_allow_resetlog_corruption"=true scope=spfile;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "clone, database
"
RMAN-01008: the bad identifier was: system
RMAN-01007: at line 1 column 7 file: standard input

RMAN> exit

Recovery Manager complete.

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 5 22:51:54 2020

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 system set "_allow_resetlog_corruption"=true scope=spfile;
alter system set "_allow_resetlog_corruption"=true scope=spfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
alter system set "_allow_resetlogs_corruption"=true scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> ;
1* select status from v$instance
SQL> ;
1* select status from v$instance
SQL> ;
1* select status from v$instance
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 2.0972E+10 bytes
Fixed Size                  2103152 bytes
Variable Size            2365589648 bytes
Database Buffers         1.8589E+10 bytes
Redo Buffers               14671872 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\MES\SYSTEM01.DBF'

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> ;
1* alter database open resetlogs
SQL> startup force;
ORACLE instance started.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 823347 generated at 05/31/2020 22:02:56 needed for thread 1
ORA-00289: suggestion : G:\ARCHIVELOG\ARC00001_1041848591.001
ORA-00280: change 823347 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL>

注意：这种方式没有借助什么的归档和rodo01.log,为了能顺利开库一定要执行：

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

这样执行完：

SQL> recover database using backup controlfile until cancel;

按cancel

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL>

posted @ 2020-06-10 16:57 attason 阅读(152) 评论(0) 收藏举报

刷新页面返回顶部

attason

WinServer2008下Oracle10G异机还原

公告