ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用RMAN恢复整个数据库

利用RMAN恢复整个数据库

Linux操作系统 作者:germany006 时间:2015-08-05 10:07:58 0 删除 编辑

利用RMAN恢复整个数据库案例一

适合场合:恢复的目录一致,同时备份的过程中有归档日志

恢复的数据库目录和down机的数据库一致,还有一个就是RMAN备份的时候已经备份了归档日志。

备份脚本:

run{

allocalte channel ch1 type disk;

sql ‘alter system archive log current’;

backup as compressed backupset database format ‘/u02/rman/testdb_%T_%U’;

sql ‘alter system archive log current’;

backup as compressed backupset archivelog all format ‘/u02/rman/testarc_%T_%U’;

bakup current controlfile format ‘/u02/rman/testcon_%T_%U’;

release channel ch1;

}

delete force noprompt expired backup;

delete force noprompt obsoelet;

 

1.    安装Oracle服务器软件

版本尽量对应,安装过程中选择只安装软件,不创建数据库

2.    设置环境变量

su – oracle

vim ~/.bash_profile

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/10g/db_1

export ORACLE_SID=test    --这个一定要对应

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

unset USERNAME

生效:source ~/.bash_profile

3.    创建相应的目录和文件,和之前服务器的一致,信息可以从rman备份的log文件看到,包括如下:

mkdir $ORACLE_BASE/admin/SID/{a,b,c,d,u}dump –p

mkdir $ORACLE_BASE/admin/SID/pfile–p

mkdir $ORACLE_BASE/oradata/SID –p

创建口令文件:

orapwd file=$ORACLE_HOME/dbs/orapwtest password=oracle entires=10

4.    拷贝备份和归档到恢复机器对应的路径下,特别是rman备份的路径,否则会报错

5.    配置主机名,监听,IP

6.    恢复数据库的过程

1)rman target / nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Sep 16 13:55:31 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

2)RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/10g/db_1/dbs/inittest.ora'

starting Oracle instance without parameter file for retrival of spfile

Oracle instance started

上面的意思是说:没有实例需要的初始化参数文件,由于没有参数文件,所以会报错,但实例已经启动

Total System Global Area     159383552 bytes

Fixed Size                     1266344 bytes

Variable Size                 54529368 bytes

Database Buffers             100663296 bytes

Redo Buffers                   2924544 bytes

3)恢复初始化参数文件

这里需要注意的是,要找对对应的备份文件,逐一尝试,一般找备份比较后而且小的

RMAN> restore spfile from '/u02/rman/testdb_20100916_05lo1lg0_1_1';

Starting restore at 16-SEP-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /u02/rman/testdb_20100916_05lo1lg0_1_1

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 16-SEP-10

4)恢复控制文件

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)

Oracle instance started

Total System Global Area     167772160 bytes

Fixed Size                     1266392 bytes

Variable Size                 62917928 bytes

Database Buffers             100663296 bytes

Redo Buffers                   2924544 bytes

RMAN> restore controlfile from '/u02/rman/testcon_20100916_07lo1lg8_1_1';

Starting restore at 16-SEP-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=101 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

output filename=/u01/app/oracle/oradata/test/control01.ctl

output filename=/u01/app/oracle/oradata/test/control02.ctl

output filename=/u01/app/oracle/oradata/test/control03.ctl

Finished restore at 16-SEP-10

5)将数据启动到mount状态

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

6)恢复数据库

后面出现的错误是指个别日志不同步,所以最好就是把归档一起拷贝全

RMAN> run{

2> restore database;

3> recover database;

4> }

7)以resetlogs打开数据库

 [oracle@sdb dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Sep 16 14:02:58 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open resetlogs;

Database altered.

若在6)数据库恢复的过程中出现问题,可以参考如下的步骤进行

在恢复完控制文件后,启动到mount状态下,在sqlplus提示符下运行如下查询

1.  sql>select file#,checkpoint_change# from v$datafile;

注:如果出现科学记数法显示不了checkpoint_change#列的时候,可以通过如下命令让其显示完全

set numw 15;

发现所有数据文件的checkpoint scn是52873354,因此需要scn大于52873354的归档日志来修复数据库

2.在rman提示符下运行list backup of archivelog all,发现scn大于52873354的归档日志的sequence范围是1052至1097,在rman提示符下运行

restore archivelog sequence between 1052 and 1097;    恢复归档日志(mount)

3.在sqlplus提示符下运行命令alter database flashback off;关闭flashback,然后在rman提示符下运行如下命令块修复数据库

rman>Run {

Set until sequence=1098;

retore database;

Recover database;

}

 

 

利用RMAN恢复整个数据库案例二  

适合场合:目录一致,没有进行归档的备份

恢复的数据库目录和down机的数据库一致,RMAN备份的时候没有备份归档,恢复的时候,应该尽可能的把数据库做全备的开始的所有后面的归档尽可能拷贝过去,这个和目前公司的有些服务器备份策略一致

说明:做数据库备份的前,用户test/test下只有hello,hell2,hello4,hello3表,备份完成后创建了表hello5,结合RMAN和归档日志进行恢复,验证备份恢复完以后,hello5表也存在

备份脚本:

run{

allocate channel ch1 type disk;

sql ‘alter system archive log current’;

backup as compressed backupset database format ‘/u02/rman/testdb_%T_%U’;

sql ‘alter system archive log current’;

backup current controlfile format ‘/u02/rman/testcon_%T_%U’;

release channel ch1;

}

 

恢复过程如下:

其中1,2,3,4和上面的过程一样,下面是步骤5恢复数据的过程:

6.利用rman恢复

[oracle@sdb test]$ rman target / nocatalog

 

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Sep 19 12:02:47 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

 

1)恢复spfile,一般找小的备份文件

RMAN> startup nomount;                                                                                                             

 

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/10g/db_1/dbs/inittest.ora'

 

starting Oracle instance without parameter file for retrival of spfile

Oracle instance started

 

Total System Global Area     159383552 bytes

 

Fixed Size                     1266344 bytes

Variable Size                 54529368 bytes

Database Buffers             100663296 bytes

Redo Buffers                   2924544 bytes

 

RMAN> restore spfile from '/u02/rman/testdb_20100919_0llo9foo_1_1';

 

Starting restore at 19-SEP-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /u02/rman/testdb_20100919_0llo9foo_1_1

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 19-SEP-10

 

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

 

connected to target database (not started)

Oracle instance started

Total System Global Area     167772160 bytes

 

Fixed Size                     1266392 bytes

Variable Size                 75500840 bytes

Database Buffers              88080384 bytes

Redo Buffers                   2924544 bytes

--说明恢复成功

 

2)恢复控制文件

RMAN> restore controlfile from '/u02/rman/testcon_20100919_0mlo9fov_1_1';

 

Starting restore at 19-SEP-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=101 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

output filename=/u01/app/oracle/oradata/test/control01.ctl

output filename=/u01/app/oracle/oradata/test/control02.ctl

output filename=/u01/app/oracle/oradata/test/control03.ctl

Finished restore at 19-SEP-10

 

3)启动数据库到mount状态,恢复数据库

RMAN> alter database mount;

 

database mounted

released channel: ORA_DISK_1

 

 

RMAN> run{

2> allocate channel ch1 type disk;

3> restore database;

4> recover database;

5> release channel ch1;

6> }

 

allocated channel: ch1

channel ch1: sid=101 devtype=DISK

 

Starting restore at 19-SEP-10

 

channel ch1: starting datafile backupset restore

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

restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf

restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf

restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf

restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf

channel ch1: reading from backup piece /u02/rman/testdb_20100919_0klo9fna_1_1

channel ch1: restored backup piece 1

piece handle=/u02/rman/testdb_20100919_0klo9fna_1_1 tag=TAG20100919T110514

channel ch1: restore complete, elapsed time: 00:00:36

Finished restore at 19-SEP-10

 

Starting recover at 19-SEP-10

 

starting media recovery

 

released channel: ch1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 09/19/2010 13:36:12

RMAN-06053: unable to perform. media recovery because of missing log

RMAN-06025: no backup of log thread 1 seq 38 lowscn 493029 found to restore

RMAN-06025: no backup of log thread 1 seq 37 lowscn 492908 found to restore

RMAN-06025: no backup of log thread 1 seq 36 lowscn 492874 found to restore

--上面说没有找到归档文件,将尽可能的归档文件拷贝过去

RMAN> exit

 

4)利用SQL*PLUS来恢复归档日志中的内容

Recovery Manager complete.

[oracle@sdb test]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Sep 19 13:36:28 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> recover database using backup controlfile;

ORA-00279: change 492881 generated at 09/19/2010 11:05:14 needed for thread 1

ORA-00289: suggestion : /u01/arclog/1_36_729861888.dbf

ORA-00280: change 492881 for thread 1 is in sequence #36

先AUTO再CANCLE

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 492908 generated at 09/19/2010 11:06:07 needed for thread 1

ORA-00289: suggestion : /u01/arclog/1_37_729861888.dbf

ORA-00280: change 492908 for thread 1 is in sequence #37

ORA-00278: log file '/u01/arclog/1_36_729861888.dbf' no longer needed for this

recovery

 

ORA-00279: change 493029 generated at 09/19/2010 11:07:40 needed for thread 1

ORA-00289: suggestion : /u01/arclog/1_38_729861888.dbf

ORA-00280: change 493029 for thread 1 is in sequence #38

ORA-00278: log file '/u01/arclog/1_37_729861888.dbf' no longer needed for this

recovery

 

ORA-00308: cannot open archived log '/u01/arclog/1_38_729861888.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

SQL> recover database using backup controlfile;

ORA-00279: change 493029 generated at 09/19/2010 11:07:40 needed for thread 1

ORA-00289: suggestion : /u01/arclog/1_38_729861888.dbf

ORA-00280: change 493029 for thread 1 is in sequence #38

 

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/test/system01.dbf'

--上面的出错信息很正常说,system01.dbf文件需要进行media recovery,由于刚才应用了归档里面的信息,所以会报错,再进去RMAN中recover database即可

 

 

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@sdb test]$ rman target / nocatalog;

 

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Sep 19 13:38:51 2010

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

connected to target database: TEST (DBID=2028008317, not open)

using target database control file instead of recovery catalog

 

RMAN> recover database;

 

Starting recover at 19-SEP-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=104 devtype=DISK

 

starting media recovery

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 09/19/2010 13:38:58

RMAN-06053: unable to perform. media recovery because of missing log

RMAN-06025: no backup of log thread 1 seq 38 lowscn 493029 found to restore

--上面出错的信息是说,没有找到seq 38号的日志,不影响

RMAN> exit

 

 

Recovery Manager complete.

 

5)测试恢复成功

[oracle@sdb test]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Sep 19 13:39:06 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL>shutdown immediate;   --最好先执行关闭再

SQL>startup;

SQL> alter database open resetlogs;

Database altered.

 

SQL>conn test/test;

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

HELLO2

HELLO

HELLO3

HELLO4

HELLO5

SQL> select count(*) from hello5;

 

  COUNT(*)

----------

153600

 

其实从3),4),5)步骤可以这样恢复,首先将尽可能的归档拷贝到归档的相应目录下

然后进行如下的操作步骤:

rman>alter database mount

rman>run{

restore database;

}

rman>catalog start with ‘/u03/backup/arcbak/’

rman>recover database;

rman>shutdown immediate;

rman>startup;

rman>alter database open reseglogs;

 

方法三:直接把那些归档拷贝到和原库一样的目录,然后执行如下的操作:

rman>alter database mount

rman>run{

restore database;

recover database;

}

rman>alter database open resetlogs;

rman>shutdown immediate;

rman>startup;

最后要检查下ps –ef |grep ora_实例进程数,如果进程过多,不用过于关注,只是数据库没有完全恢复的原因

 

 

利用RMAN恢复整个数据库案例三

恢复目录不一致

当数据库目录不一样的时候,一般没有必要这样做,但是有时候特殊的需要

备份脚本:

run{

allocate channel ch1 type disk;

sql ‘alter system archive log current’;

backup as compressed backupset database format ‘/u02/rman/testdb_%T_%U’;

sql ‘alter system archive log current’;

backup as compressed archivelog all format ‘/u02/rman/testarc_%T_%U’;

backup current controlfile format ‘/u02/rman/testcon_%T_U’;

release channel ch1;

}

crosscheck backup;

delete force noprompt obsolete;

 

1,2,3,4,5过程和案例一的基本一样。

下面是恢复过程:

[oracle@sdb rman]$ rman target / nocatalog

 

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Sep 19 17:08:33 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

 

1)恢复spfile文件

RMAN> startup nomount;

connected to target database (not started)

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/10g/db_1/dbs/inittest.ora'

 

starting Oracle instance without parameter file for retrival of spfile

Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     1266344 bytes

Variable Size                 54529368 bytes

Database Buffers             100663296 bytes

Redo Buffers                   2924544 bytes

 

RMAN> restore spfile from '/u02/rman/testdb_20100919_0slo9uv5_1_1';

 

Starting restore at 19-SEP-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /u02/rman/testdb_20100919_0slo9uv5_1_1

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 19-SEP-10

 

3)恢复controlfile

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

 

connected to target database (not started)

Oracle instance started

Total System Global Area     167772160 bytes

Fixed Size                     1266392 bytes

Variable Size                 88083752 bytes

Database Buffers              75497472 bytes

Redo Buffers                   2924544 bytes

 

RMAN> restore controlfile from '/u02/rman/testcon_20100919_0ulo9uvd_1_1';

Starting restore at 19-SEP-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=101 devtype=DISK

channel ORA_DISK_1: restoring control file

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

output filename=/u01/app/oracle/oradata/test/control01.ctl

output filename=/u01/app/oracle/oradata/test/control02.ctl

output filename=/u01/app/oracle/oradata/test/control03.ctl

Finished restore at 19-SEP-10

 

4)恢复数据库(restore,recover)

RMAN> alter database mount;

 

database mounted

released channel: ORA_DISK_1

 

RMAN> report schema;

 

Starting implicit crosscheck backup at 19-SEP-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=101 devtype=DISK

Crosschecked 7 objects

Finished implicit crosscheck backup at 19-SEP-10

Starting implicit crosscheck copy at 19-SEP-10

using channel ORA_DISK_1

Finished implicit crosscheck copy at 19-SEP-10

searching for all files in the recovery area

cataloging files...

no files cataloged

 

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema

 

List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    0        SYSTEM               ***     /u01/app/oracle/oradata/test/system01.dbf

2    0        UNDOTBS1             ***     /u01/app/oracle/oradata/test/undotbs01.dbf

3    0        SYSAUX               ***     /u01/app/oracle/oradata/test/sysaux01.dbf

4    0        USERS                ***     /u01/app/oracle/oradata/test/users01.dbf

 

List of Temporary Files

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

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    0        TEMP                 32767       /u01/app/oracle/oradata/test/temp01.dbf

 

RMAN> run{

2> set newname for datafile 1 to '/u03/oradata/test/system01.dbf';

3> set newname for datafile 2 to '/u03/oradata/test/undotbs01.dbf';

4> set newname for datafile 3 to '/u03/oradata/test/sysaux01.dbf';

5> set newname for datafile 4 to '/u03/oradata/test/users01.dbf';

6> restore datafile 1,2,3,4;

7> switch datafile all;

8> }    

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 19-SEP-10

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 /u03/oradata/test/system01.dbf

restoring datafile 00002 to /u03/oradata/test/undotbs01.dbf

restoring datafile 00003 to /u03/oradata/test/sysaux01.dbf

restoring datafile 00004 to /u03/oradata/test/users01.dbf

channel ORA_DISK_1: reading from backup piece /u02/rman/testdb_20100919_0rlo9utn_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/u02/rman/testdb_20100919_0rlo9utn_1_1 tag=TAG20100919T152439

channel ORA_DISK_1: restore complete, elapsed time: 00:00:36

Finished restore at 19-SEP-10

datafile 1 switched to datafile copy

input datafile copy recid=5 stamp=730143305 filename=/u03/oradata/test/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=6 stamp=730143305 filename=/u03/oradata/test/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=7 stamp=730143305 filename=/u03/oradata/test/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=8 stamp=730143305 filename=/u03/oradata/test/users01.dbf

 

RMAN> alter database mount;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 09/19/2010 17:35:25

ORA-01100: database already mounted

 

RMAN> recover database;

 

Starting recover at 19-SEP-10

using channel ORA_DISK_1

 

starting media recovery

 

archive log thread 1 sequence 43 is already on disk as file /u01/arclog/1_43_729861888.dbf

archive log thread 1 sequence 44 is already on disk as file /u01/arclog/1_44_729861888.dbf

archive log filename=/u01/arclog/1_43_729861888.dbf thread=1 sequence=43

archive log filename=/u01/arclog/1_44_729861888.dbf thread=1 sequence=44

unable to find archive log

archive log thread=1 sequence=45

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 09/19/2010 17:36:40

RMAN-06054: media recovery requesting unknown log: thread 1 seq 45 lowscn 500916

 

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.

 

5)到此为至,数据文件,以及初始化参数文件,控制文件已经恢复完毕,但控制文件还是保存在以前记录的目录里,以及redo,temp文件,下面是这几个文件的恢复过程:

[oracle@sdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Sep 19 17:37:44 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

A.查看当前日志的状态

SQL> select group#,status from v$log;

 

删除不在使用的归档日志,然后再添加回去

SQL> alter database drop logfile group 2;

 

SQL> alter database add logfile group 2 '/u03/oradata/test/redo02.log' size 50m;

 

SQL> alter database drop logfile group 3;

 

SQL> alter database add logfile group 3 '/u03/oradata/test/redo03.log' size 50m;

 

下面删除归档1的时候要注意,必须将其状态切换变为INACTIVE的时候,才可以删除归档1,否则会提示当前的还没有归档完

SQL> select group#,status from v$log;

SQL> alter system archive log current;

SQL> select group#,status from v$log;

 

SQL> alter system archive log current;

SQL> select group#,status from v$log;

 

SQL> alter system archive log current;

SQL> select group#,status from v$log;

 

SQL> alter system archive log current;

SQL> select group#,status from v$log;

 

SQL> alter database drop logfile group 1;

 

SQL> alter database add logfile group 1 '/u03/oradata/test/redo01.log' size 50m;

 

SQL> select * from v$logfile;

看上面的信息redo联机日志恢复成功

 

B.恢复controlfile文件

SQL> create pfile='/home/oracle/inittest.ora' from spfile;    --先备份spfile 

SQL> shutdown immediate;

 

[oracle@sdb ~]$ vim inittest.ora   --加颜色的地方,就是要修改的地方,像*dump_dest目录也可以根据需要进行修改,还有归档目录

 

test.__db_cache_size=75497472

test.__java_pool_size=4194304

test.__large_pool_size=4194304

test.__shared_pool_size=79691776

test.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/test/adump'

*.background_dump_dest='/u01/app/oracle/admin/test/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/u03/oradata/test/control01.ctl','/u03/oradata/test/control02.ctl','/u03/oradata/test/control03.ctl'#Restore Controlfile

*.core_dump_dest='/u01/app/oracle/admin/test/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='test'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=/u03/arclog'

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=100

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=115

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/test/udump'

 

 [oracle@sdb ~]$ sqlplus / as sysdba

 SQL> startup nomount pfile='/home/oracle/inittest.ora';

 

[oracle@sdb ~]$ cd /u01/app/oracle/oradata/test/

 

下面是重点,一定要将控制文件拷贝到恢复后相应的目录里

[oracle@sdb test]$ cp ./control* /u03/oradata/test/

[oracle@sdb test]$ sqlplus / as sysdba

SQL> alter database mount;

SQL> alter database open;

 

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

'/u01/app/oracle/10g/db_1/dbs/inittest.ora'

出现上面的错误原因是:在$ORACLE_HOME/dbs目录下没有找到pfile文件,将之前在/home/oracle/inittest.ora文件拷贝过去,再执行

 

SQL> create spfile from pfile;

 

C.修改temp文件:

先将数据文件离线,再修改名以及路径,最后让数据文件在线

SQL> alter database tempfile '/u01/app/oracle/oradata/test/temp01.dbf' offline;

 

SQL> host cp /u01/app/oracle/oradata/test/temp01.dbf /u03/oradata/test/temp01.dbf;

SQL> alter database rename file '/u01/app/oracle/oradata/test/temp01.dbf' to '/u03/oradata/test/temp01.dbf';

SQL> alter database tempfile '/u03/oradata/test/temp01.dbf' online;

 

5)检查恢复的整体情况,当然也可以再进行下归档的恢复

SQL> shutdown immediate;

SQL> startup;

SQL> select name from v$datafile;

SQL> select group#,member from v$logfile;

SQL> select name from v$controlfile;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28371090/viewspace-1762271/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ORACLE,MYSQL,POSTGRESQL,SQLSERVER

注册时间:2013-03-06

  • 博文量
    763
  • 访问量
    916755