利用RMAN恢復整個資料庫

germany006發表於2015-08-05

利用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#, 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表也存在

備份指令碼:

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章