oracle恢复大全

川长思鸟来 2022-09-23 05:59 188阅读 0赞

异机恢复在以下不同情况下处理方式不同：

一、当Oracle 参数文件，控制文件，数据文件，日志文件，密码文件都正常，且目标机器和源机器安装目录（即环境变量）都一致，则直接拷贝文件到相应目录，然后启动数据库即可；

二、当Oracle 参数文件，控制文件，数据文件，日志文件，密码文件都正常，但目标机器和源机器安装目录不一致，则直接拷贝文件到相应目录：

1. 首先vi pfile，将pfile里面的control file 和audit file，闪回区路径都改成目标机器上的路径；

2. sqlplus  / as sysdba

利用pfile 创建spfile：SQL>create spfile from pfile='pfile 路径';

3. 然后startup nomount ——>没报错，继续alter database mount；

4. 由于控制文件中记录了数据文件（临时文件除外），日志文件的路径，所以需要在mount 状态下将这些文件路径修改正确；

alter database rename file ‘old\_file\_name’  to ‘new\_file\_name'；

5. 然后再alter database open；这一步成功了，表示数据库正常启动了，查看状态，如果报错，则根据报错信息具体问题具体对待；

（注意：在打开数据库时，如果redolog路径正常，且权限正常，磁盘正常，都不需要做resetlog操作，resetlog 是将redo的sequence号只清空，从头开始写redolog。）

三、当Oracle 参数文件，控制文件，数据文件，日志文件，密码文件中某些文件有损坏情况下恢复操作：

1. 参数文件损坏：

startup nomount启动方式，查找文件的顺序是 spfileSID.ora-〉spfile.ora-〉initSID.ora-〉init.ora（spfile优先于pfile）

如果spfile 损坏，可以通过pfile来创建spfile，如果所有的spfile和pfile都损坏或丢失，则需要手动创建init.ora 参数文件，里面要写明控制文件等参数的正确路径，再启动到nomount下；

2. 控制文件损坏：

2-1.  损坏一个控制文件：

一般数据库里面控制文件会有多个副本，如果只是其中一个控制文件损坏，则可以拷贝正常的控制文件；

2.2.  如果所有的控制文件都损坏，则需要重建控制文件。如果系统上有重建控制文件的脚本，则可以用脚本重建控制文件，否则需要自定义脚本，修改其中数据文件，日志文件的路径来重建控制文件。

alter database backup controlfile to trace as '/home/oracle/crontol\_trace.trc'; 该命令需要在mount状态下才能执行，如果当控制文件坏了，只能到nomount状态下时，就自定义脚本：

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "EASTMONEY" NORESETLOGS  NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 4672

LOGFILE

GROUP 1 '/opt/app/oracle/oradata/eastmoney/redo01.log'  SIZE 1024M,

GROUP 2 '/opt/app/oracle/oradata/eastmoney/redo02.log'  SIZE 1024M,

GROUP 3 '/opt/app/oracle/oradata/eastmoney/redo03.log'  SIZE 1024M

\-- STANDBY LOGFILE

DATAFILE

'/opt/app/oracle/oradata/eastmoney/system01.dbf',

'/opt/app/oracle/oradata/eastmoney/undotbs01.dbf',

'/opt/app/oracle/oradata/eastmoney/sysaux01.dbf',

'/opt/app/oracle/oradata/eastmoney/users01.dbf',

'/opt/app/oracle/oradata/eastmoney/dev03.dbf'

CHARACTER SET ZHS16GBK;

除了以上重建控制文件方法外，如果有控制文件的 备份和数据库的备份，可以将其恢复出来来重建控制文件：

模拟情景：

SQL>ho rm /u01/orcl/control01.ctl

SQL>ho rm /u01/orcl/control02.ctl

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2253664 bytes

Variable Size             989858976 bytes

Database Buffers          603979776 bytes

Redo Buffers                7319552 bytes

SQL>

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01507: database not mounted

SQL> alter database mount;

alter database mount

ERROR at line 1:

ORA-00205: error in identifying control file, check alert log for more info

解决方法：

如果之前备份控制文件是是nocatalog模式，则restore controlfile命令会报错，必须要以autobackup来恢复，除非自己指定备份集恢复；

rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 29 16:34:31 2016

^\[\[3~connected to target database: EMTEST ( DBID=1493189685 )

using target database control file instead of recovery catalog

红色是指数据库id

RMAN> set dbid 1788360963

executing command: SET DBID

RMAN> restore controlfile from '/u01/app/oracle/fast\_recovery\_area/ORACLE/backupset/2016\_06\_27/o1\_mf\_ncsnf\_TAG20160627T093153\_cq10jkp9\_.bkp';

Starting restore at 27-JUN-16

using channel ORA\_DISK\_1

channel ORA\_DISK\_1: restoring control file

channel ORA\_DISK\_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/oracle/control01.ctl

output file name=/u01/app/oracle/fast\_recovery\_area/oracle/control02.ctl

Finished restore at 27-JUN-16

RMAN> sql 'alter database mount';

RMAN> restore database;

RMAN> recover database;

RMAN> sql 'alter database open';

注意：如果备份是以catalog模式备份的，即使没有打开controlfile的自动备份，进行restore controlfile时也不会报出如下错误：

3. 数据文件损坏：

1）、数据库在非归档有一个过去的冷备份情况下，数据文件丢失了，而且在线重做日志切换过多次，则会导致在冷备到数据文件丢失时刻的数据都会丢失；

恢复办法只能将冷备数据全部拷贝到数据库文件目录，恢复，只能恢复到冷备时间点的数据。而且恢复必须要把当时冷备的控制文件和数据文件，日志文件全部拿出来恢复，否则三者文件头不一致会报错。

2）、数据库在归档 有数据库的全备和归档日志的备份

3-2-1、丢失一个数据文件的情况：

模拟情景：

SQL>ho rm /u01/orcl/users01.dbf

SQL> shutdown immediate

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u01/orcl/users01.dbf'

ORA-27041: unable to open file

Linux-x86\_64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2253664 bytes

Variable Size             989858976 bytes

Database Buffers          603979776 bytes

Redo Buffers                7319552 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/u01/orcl/users01.dbf'

恢复语法：

\[oracle@oracle 2016\_06\_24\]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 24 16:13:15 2016

connected to target database: ORACLE (DBID=1788360963, not open)

RMAN> run \{

2> allocate channel c1 type disk;

3> restore datafile 4;

4> recover datafile 4;

5> sql 'alter database datafile 4 online';

6> sql 'alter database open';

7> release channel c1;

8> \}

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=20 device type=DISK

Starting restore at 24-JUN-16

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00004 to /u01/orcl/users01.dbf

channel c1: reading from backup piece /u01/app/oracle/fast\_recovery\_area/ORACLE/backupset/2016\_06\_24/o1\_mf\_nnndf\_TAG20160624T151220\_cpsqbnp0\_.bkp

channel c1: piece handle=/u01/app/oracle/fast\_recovery\_area/ORACLE/backupset/2016\_06\_24/o1\_mf\_nnndf\_TAG20160624T151220\_cpsqbnp0\_.bkp tag=TAG20160624T151220

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:01

Finished restore at 24-JUN-16

Starting recover at 24-JUN-16

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 24-JUN-16

sql statement: alter database datafile 4 online

sql statement: alter database open

released channel: c1

SQL> select open\_mode from v$database;

OPEN\_MODE

\------------------------------------------------------------

READ WRITE

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

3-2-2、丢失所有数据文件的情况：

模拟情景：

SQL> ho rm /u01/orcl/\*.dbf

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2253664 bytes

Variable Size             989858976 bytes

Database Buffers          603979776 bytes

Redo Buffers                7319552 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/orcl/system01.dbf'

恢复语法：

RMAN> run\{

2> allocate channel c1 type disk;

3> restore database;

4> recover database;

5> sql 'alter database open';

6> release channel c1;

7> \}

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=20 device type=DISK

Starting restore at 24-JUN-16

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00001 to /u01/orcl/system01.dbf

channel c1: restoring datafile 00002 to /u01/orcl/sysaux01.dbf

channel c1: restoring datafile 00003 to /u01/orcl/undotbs01.dbf

channel c1: restoring datafile 00004 to /u01/orcl/users01.dbf

channel c1: reading from backup piece /u01/app/oracle/fast\_recovery\_area/ORACLE/backupset/2016\_06\_24/o1\_mf\_nnndf\_TAG20160624T151220\_cpsqbnp0\_.bkp

channel c1: piece handle=/u01/app/oracle/fast\_recovery\_area/ORACLE/backupset/2016\_06\_24/o1\_mf\_nnndf\_TAG20160624T151220\_cpsqbnp0\_.bkp tag=TAG20160624T151220

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:07

Finished restore at 24-JUN-16

Starting recover at 24-JUN-16

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 24-JUN-16

sql statement: alter database open

released channel: c1

SQL> select open\_mode from v$database;

OPEN\_MODE

\------------------------------------------------------------

READ WRITE

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

3-2-3.  损坏非当前联机日志成员（非current状态）：

模拟情景：

SQL> ho rm  /u01/orcl/redo03\_1.log

SQL> shutdown immeidate;

SP2-0717: illegal SHUTDOWN option

SQL> shutdown immeidate

SP2-0717: illegal SHUTDOWN option

SQL>

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2253664 bytes

Variable Size             989858976 bytes

Database Buffers          603979776 bytes

Redo Buffers                7319552 bytes

Database mounted.

Database opened.

SQL> select \* from v$logfile;

GROUP\# STATUS                TYPE                  MEMBER                                             IS\_RECOVE

\---------- --------------------- --------------------- -------------------------------------------------- ---------

2                       ONLINE                /u01/orcl/redo02.log                               NO

1                       ONLINE                /u01/orcl/redo01.log                               NO

3                       ONLINE                /u01/orcl/redo03.log                               NO

1                       ONLINE                /u01/orcl/redo01\_1.log                             NO

2                       ONLINE                /u01/orcl/redo02\_1.log                             NO

3 INVALID         ONLINE                /u01/orcl/redo03\_1.log                             NO

解决方法：

SQL> alter database drop logfile member '/u01/orcl/redo03\_1.log';

Database altered.

SQL> alter database add logfile member '/u01/orcl/redo03\_1.log' to group 3;

Database altered.

SQL> select \* from v$logfile;

GROUP\# STATUS                TYPE                  MEMBER                                             IS\_RECOVE

\---------- --------------------- --------------------- -------------------------------------------------- ---------

2                       ONLINE                /u01/orcl/redo02.log                               NO

1                       ONLINE                /u01/orcl/redo01.log                               NO

3                       ONLINE                /u01/orcl/redo03.log                               NO

1                       ONLINE                /u01/orcl/redo01\_1.log                             NO

2                       ONLINE                /u01/orcl/redo02\_1.log                             NO

3                       ONLINE                /u01/orcl/redo03\_1.log                             NO

3-2-4.  损坏非当前联机日志组

模拟情景：

SQL> ho rm /u01/orcl/redo02.log

SQL> ho rm /u01/orcl/redo02\_1.log

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2253664 bytes

Variable Size             989858976 bytes

Database Buffers          603979776 bytes

Redo Buffers                7319552 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 11005

Session ID: 1 Serial number: 5

解决方法

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2253664 bytes

Variable Size             989858976 bytes

Database Buffers          603979776 bytes

Redo Buffers                7319552 bytes

Database mounted.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database open;

Database altered.

注意：

如果该日志组还没有归档，则用

alter database clear unarchived logfile group 2;

3-3-5.  损坏所有联机日志：

模拟情景：

SQL> ho rm /u01/orcl/\*.log

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2253664 bytes

Variable Size             989858976 bytes

Database Buffers          603979776 bytes

Redo Buffers                7319552 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 11155

Session ID: 1 Serial number: 5

解决方法：

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2253664 bytes

Variable Size             989858976 bytes

Database Buffers          603979776 bytes

Redo Buffers                7319552 bytes

Database mounted.

SQL> alter system set "\_allow\_resetlogs\_corruption"=true scope =spfile；

System altered.

SQL> recover database until cancel;

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select open\_mode from v$database;

OPEN\_MODE

\------------------------------------------------------------

READ WRITE

4. 临时文件损坏：

模拟情景：

SQL> ho rm /u01/orcl/temp01.dbf

SQL> shutdown immeidate

SQL> startup

SQL>select file\_name,tablespace\_name from dba\_temp\_files;

如果此时临时文件自动出来了，则不需要恢复，如果重启数据库之后，临时文件没有出来，则需要删掉以前的，重新给临时表空间添加临时文件；

SQL> alter database tempfile '/u01/orcl/temp01.dbf' drop;

Database altered.

SQL> alter tablespace temp1 add tempfile '/u01/orcl/temp01.dbf' size 10M;

Tablespace altered.

oracle恢复大全

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle数据恢复

相关 oracle恢复大全

相关 Oracle实例恢复

相关 Oracle恢复流程

相关 Oracle函数大全

相关 Oracle语句大全

相关 Oracle函数大全

相关 oracle 语句大全

相关 Oracle数据恢复、数据库恢复、灾难恢复专题

相关 [ORACLE] 函数大全