Oracle 備份恢復實例：三思筆記 1 shutdown abort 系統歸檔模式，有備份 create table xx as select * from emp; update xx set ename ='xxxxxx'; select * from xx [oracle@localhost ...

Oracle 備份恢復實例：三思筆記

1 shutdown abort

系統歸檔模式，有備份

create table xx as

select * from emp;

update xx set ename ='xxxxxx';

select * from xx

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 19 14:13:27 2014

SQL> conn /as sysdba

Connected.

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup;---啟動需要實例恢復

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 79693392 bytes

Database Buffers 201326592 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

--更新了未提交的數據，重新open後，被回滾

2 shutdown immediate，undo丟失

update xx set ename ='xxxxxx';

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@localhost grs]$ mv undotbs01.dbf /u01/oracle/backup/

SQL> startup;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 79693392 bytes

Database Buffers 201326592 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 2: '/u01/app/oracle/oradata/grs/undotbs01.dbf'

[oracle@localhost grs]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 19 14:38:53 2014

connected to target database: GRS (DBID=325518186, not open)

RMAN> restore datafile 2;

Starting restore at 19-MAR-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

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

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

channel ORA_DISK_1: reading from backup piece /u01/oracle/backup/rman1104_backup_1tp3gc5t_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/oracle/backup/rman1104_backup_1tp3gc5t_1_1 tag=TAG20140318T154813

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

Finished restore at 19-MAR-14

RMAN> recover datafile 2;

Starting recover at 19-MAR-14

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 61 is already on disk as file /u01/app/oracle/archivelog/archivelog/1_61_830623455.dbf

archive log thread 1 sequence 62 is already on disk as file /u01/app/oracle/archivelog/archivelog/1_62_830623455.dbf

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=60

channel ORA_DISK_1: reading from backup piece /u01/oracle/backup/rman1104_backup_1up3gc8s_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/oracle/backup/rman1104_backup_1up3gc8s_1_1 tag=TAG20140318T154948

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

archive log filename=/u01/app/oracle/archivelog/archivelog/1_60_830623455.dbf thread=1 sequence=60

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

Finished recover at 19-MAR-14

RMAN> alter database open;

database opened

RMAN>

3 undo丟失，有備份

[oracle@localhost grs]$ rm undotbs01.dbf

SQL> shutdown immediate;

ORA-01116: error in opening database file 2

ORA-01110: data file 2: '/u01/app/oracle/oradata/grs/undotbs01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

RMAN> restore datafile 2;

Starting restore at 19-MAR-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

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

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

channel ORA_DISK_1: reading from backup piece /u01/oracle/backup/rman1104_backup_1tp3gc5t_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/oracle/backup/rman1104_backup_1tp3gc5t_1_1 tag=TAG20140318T154813

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

Finished restore at 19-MAR-14

RMAN> recover datafile 2;

Starting recover at 19-MAR-14

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 60 is already on disk as file /u01/app/oracle/archivelog/archivelog/1_60_830623455.dbf

archive log thread 1 sequence 61 is already on disk as file /u01/app/oracle/archivelog/archivelog/1_61_830623455.dbf

archive log thread 1 sequence 62 is already on disk as file /u01/app/oracle/archivelog/archivelog/1_62_830623455.dbf

archive log filename=/u01/app/oracle/archivelog/archivelog/1_60_830623455.dbf thread=1 sequence=60

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

Finished recover at 19-MAR-14

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 71304784 bytes

Database Buffers 209715200 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> alter database datafile 2 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database datafile 2 online;

Database altered.

4 shutdown abort，無備份，undo丟失

update xx set ename ='xxxxxx';

select s.sid,s.serial#,t.used_ublk,r.status,s.username,r.segment_name

from gv$transaction t, gv$session s, dba_rollback_segs r

where t.ses_addr = s.saddr and t.xidusn = r.segment_id and r.tablespace_name='UNDOTBS1';

144 5 1 ONLINE SCOTT _SYSSMU11$

SQL> shutdown abort;

ORACLE instance shut down.

[oracle@localhost grs]$ mv undotbs01.dbf /u01/oracle/backup/

SQL> startup;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 71304784 bytes

Database Buffers 209715200 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 2: '/u01/app/oracle/oradata/grs/undotbs01.dbf'

SQL> col error format a15

SQL> set linesize 1000

SQL> set pagesize 1000

SQL> select * from v$recover_file;--查看那個文件需要恢復

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

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

2 ONLINE ONLINE FILE NOT FOUND 0

SQL> alter database datafile 2 offline drop;

---設置為offline並刪除（邏輯上）

Database altered.

SQL> alter database open;

--db可以打開

Database altered.

SQL> conn

Enter user-name: scott

Enter password:

Connected.

SQL> select * from xx;

select * from xx

ERROR at line 1:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/grs/undotbs01.dbf'

SQL> select * from v$datafile;--能看見刪除的undo dbf

create pfile='/u01/app/oracle/pfile1' from spfile;

[oracle@localhost grs]$ vi /u01/app/oracle/pfile1

grs.__db_cache_size=209715200

grs.__java_pool_size=4194304

grs.__large_pool_size=4194304

grs.__shared_pool_size=58720256

grs.__streams_pool_size=4194304

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

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

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/grs/control01.ctl','/u01/app/oracle/oradata/grs/control02.ctl','/u01/app/oracle/oradata/grs/control03.ctl'#Restore Controlfile

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

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='grs'

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

*.db_recovery_file_dest_size=2147483648

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

*.job_queue_processes=10

*.log_archive_dest_1='location=/u01/app/oracle/archivelog/archivelog/'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='MANUAL'-----改為manual

*.undo_tablespace='UNDOTBS1'

*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$)----添加

*._allow_resetlogs_corruption=true---隱藏參數

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

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile='/u01/app/oracle/pfile1' mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 71304784 bytes

Database Buffers 209715200 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> recover database until cancel;

Media recovery complete.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/grs/undotbs01.dbf' OFFLINE DROP;

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS

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

SYSTEM SYS SYSTEM ONLINE

_SYSSMU11$ PUBLIC UNDOTBS1 NEEDS RECOVERY

SQL> DROP ROLLBACK SEGMENT "_SYSSMU11$";---出現次錯誤，檢查寫法是否正確，關閉db，在pfile中的參數*._corrupted_rollback_segments中增加"_SYSSMU11$"

DROP ROLLBACK SEGMENT "_SYSSMU11$"

ERROR at line 1:

ORA-30025: DROP segment '_SYSSMU11$' (in undo tablespace) not allowed

SQL> DROP ROLLBACK SEGMENT "_SYSSMU11$";

Rollback segment dropped.

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> create undo tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/grs/undotbs01.dbf' size 100m;

Tablespace created.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@localhost grs]$ vi /u01/app/oracle/pfile1----修改的另外一個pfile的副本

---去掉隱藏參數，

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

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

SQL> startup;-----這裡預設啟用spfile，

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 71304784 bytes

Database Buffers 209715200 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

5 歸檔，rman全備份，控制文件全部丟失

update xx set ename='20134978'

select * from xx

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@localhost grs]$ mv control01.ctl control02.ctl control03.ctl ./control_backup/

---移除控制文件

[oracle@localhost grs]$ ll

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 75499088 bytes

Database Buffers 205520896 bytes

Redo Buffers 2973696 bytes

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

--啟動報錯

查看alert 日誌

[oracle@localhost grs]$ tail -f -n 20 /u01/app/oracle/admin/grs/bdump/alert_grs.log

LGWR started with pid=6, OS id=28040

CKPT started with pid=7, OS id=28042

SMON started with pid=8, OS id=28044

RECO started with pid=9, OS id=28046

CJQ0 started with pid=10, OS id=28048

MMON started with pid=11, OS id=28050

Thu Mar 20 16:25:14 2014

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

MMNL started with pid=12, OS id=28052

Thu Mar 20 16:25:14 2014

starting up 1 shared server(s) ...

Thu Mar 20 16:25:15 2014

ALTER DATABASE MOUNT

Thu Mar 20 16:25:15 2014

ORA-00202: control file: '/u01/app/oracle/oradata/grs/control01.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Thu Mar 20 16:25:18 2014

ORA-205 signalled during: ALTER DATABASE MOUNT...

[oracle@localhost grs]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 20 16:26:40 2014

connected to target database: grs (not mounted)

RMAN> set DBID=325518186

executing command: SET DBID

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

RMAN> restore controlfile from '/u01/oracle/backup/rman_backup_c-325518186-20140320-01';

Starting restore at 20-MAR-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring control file

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

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

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

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

Finished restore at 20-MAR-14

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 20-MAR-14

Starting implicit crosscheck backup at 20-MAR-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

Crosschecked 4 objects

Finished implicit crosscheck backup at 20-MAR-14

Starting implicit crosscheck copy at 20-MAR-14

using channel ORA_DISK_1

Finished implicit crosscheck copy at 20-MAR-14

searching for all files in the recovery area

cataloging files...

no files cataloged

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 7 is already on disk as file /u01/app/oracle/oradata/grs/redo02.log

archive log filename=/u01/app/oracle/oradata/grs/redo02.log thread=1 sequence=7

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

Finished recover at 20-MAR-14

RMAN> alter database open resetlogs;

以控制文件恢復，需要resetlogs方式打開db

database opened

RMAN>

select * from xx

查看數據正常

6 歸檔，無備份，控制文件全部丟失

--從新創建控制文件

在internal或sys 下運行如下創建控制文件的腳本，註意完整列出聯機日誌或數據文件的路徑，或修改由 alter database backup control file to trace 備份控制文件時產生的腳本，去掉多餘的註釋即可。

SQL> alter database backup controlfile to trace as'/u01/oracle/backup/control_trace.sql';

Database altered.

user_dump_dest

alter database backup controlfile to trace as'/u01/oracle/backup/control_trace.sql';

idle> recover database using backup controlfile until cancel;

CREATE CONTROLFILE REUSE DATABASE grs2 NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u02/app/oracle/oradata/grs2/redo01.log' SIZE 50M,

GROUP 2 '/u02/app/oracle/oradata/grs2/redo02.log' SIZE 50M,

GROUP 3 '/u02/app/oracle/oradata/grs2/redo03.log' SIZE 50M

DATAFILE

'/u02/app/oracle/oradata/grs2/system01.dbf',

'/u02/app/oracle/oradata/grs2/undotbs01.dbf',

'/u02/app/oracle/oradata/grs2/sysaux01.dbf',

'/u02/app/oracle/oradata/grs2/users01.dbf',

'/u02/app/oracle/oradata/grs2/example01.dbf',

'/u02/app/oracle/oradata/grs2/yyhhqq.dbf',

'/u02/app/oracle/oradata/grs2/cmask01.dbf',

'/u02/app/oracle/oradata/grs2/rman_catalog.dbf',

'/u02/app/oracle/oradata/grs2/rman_test.dbf',

'/u02/app/oracle/oradata/grs2/data_test.dbf'

CHARACTER SET AL32UTF8

;

7 歸檔，有備份，非當前redo丟失

update xx set ename='erererl' 未commit

[oracle@localhost grs]$ rm redo03.log

SQL> startup;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 75499088 bytes

Database Buffers 205520896 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/grs/redo03.log'

SQL> set linesize 1000

SQL> set pagesize 1000

SQL> select * from v$log;----日誌3是未使用狀態

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

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

1 1 2 52428800 1 NO CURRENT 3252428 21-MAR-14

3 1 0 52428800 1 YES UNUSED 0

2 1 1 52428800 1 YES INACTIVE 3219698 20-MAR-14

SQL> alter database clear logfile group 3;---重建該組重做日誌組

如果是該日誌組還沒有歸檔，則需要用

> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database open;

Database altered.

select * from xx;---未提交的，回滾

說明：

1、如果損壞的是非當前的聯機日誌文件，一般只需要 clear 就可以重建該日誌文件，但是如果該資料庫處於歸檔狀態但該日誌還沒有歸檔，就需要強行 clear。

2、建議clear，特別是強行 clear後作一次資料庫的全備份。

3、此方法適用於歸檔與非歸檔資料庫。

http://www.itpub.net/thread-1824825-1-1.html

總結，對於不是當前使用的歸檔日誌損壞，歸檔模式需要使用alter database clear unarchived 命令清空日誌組即可。對於非歸檔模式需要使用alter system clear 日誌文件組即可。

8 丟失當前redo，歸檔，有備份

歸檔模式下當前日誌的損壞有兩種情況：

一、是資料庫是正常關閉，日誌文件中沒有未解決的事務需要實例恢復，當前日誌組的損壞就可以直接用alter database clear unarchived logfile group n 來重建。

二、是日誌組中有活動的事務，資料庫需要媒體恢復，日誌組需要用來同步，有兩種補救辦

法：

A.最好的辦法就是通過不完全恢復，可以保證資料庫的一致性，但是這種辦法要求在歸檔方式下，並且有可用的備份。

B.通過強制性恢復，但是可能導致資料庫不一致。

下麵分別用來說明這兩種恢復方法

1 歸檔有備份，採用不完全恢復

select * from v$log;

1 1 2 52428800 1 YES INACTIVE 3252428 2014-3-21 5:31:00

2 1 1 52428800 1 YES INACTIVE 3219698 2014-3-20 16:29:31

3 1 3 52428800 1 NO CURRENT 3271167 2014-3-21 14:36:45

--刪除文件3

[oracle@localhost grs]$ rm redo03.log

SQL> startup;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 75499088 bytes

Database Buffers 205520896 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/grs/redo03.log'

SQL> alter database clear unarchived logfile group 3;

alter database clear unarchived logfile group 3

ERROR at line 1:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/grs/redo03.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

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

GROUP# SEQUENCE# ARC STATUS

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

1 2 YES INACTIVE

3 0 NO CLEARING_CURRENT

2 0 YES UNUSED

RMAN> restore database;

Starting restore at 21-MAR-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=151 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

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

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

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

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

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

restoring datafile 00005 to /u01/app/oracle/oradata/grs/example01.dbf

restoring datafile 00006 to /u01/app/oracle/oradata/grs/yyhhqq.dbf

restoring datafile 00007 to /u01/app/oracle/oradata/grs/cmask01.dbf

restoring datafile 00008 to /u01/app/oracle/oradata/grs/rman_catalog.dbf

restoring datafile 00009 to /u01/app/oracle/oradata/grs/rman_test.dbf

channel ORA_DISK_1: reading from backup piece /u01/oracle/backup/rman_backup_2gp3lmi7

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/oracle/backup/rman_backup_2gp3lmi7 tag=TAG20140320T161606

channel ORA_DISK_1: restore complete, elapsed time: 00:04:28

Finished restore at 21-MAR-14

RMAN> run{

2> sql'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"';

3> set until time '2014-03-21 15:45:04';

4> recover database;

5> };

sql statement: alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"

executing command: SET until clause

Starting recover at 21-MAR-14

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /u01/app/oracle/archivelog/archivelog/1_6_842630868.dbf

archive log thread 1 sequence 7 is already on disk as file /u01/app/oracle/archivelog/archivelog/1_7_842630868.dbf

archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/archivelog/archivelog/1_1_842718571.dbf

archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/archivelog/archivelog/1_2_842718571.dbf

archive log filename=/u01/app/oracle/archivelog/archivelog/1_6_842630868.dbf thread=1 sequence=6

archive log filename=/u01/app/oracle/archivelog/archivelog/1_7_842630868.dbf thread=1 sequence=7

archive log filename=/u01/app/oracle/archivelog/archivelog/1_1_842718571.dbf thread=1 sequence=1

unable to find archive log

archive log thread=1 sequence=3

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

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

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

RMAN-03002: failure of recover command at 03/21/2014 16:06:52

RMAN-06054: media recovery requesting unknown log: thread 1 seq 3 lowscn 3271167

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

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

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

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate"

RMAN-01007: at line 0 column 2 file: standard input

RMAN>

SQL> alter database open resetlogs;

Database altered.

2 採用隱藏的初始化參數來恢復

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 75499088 bytes

Database Buffers 205520896 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/grs/redo01.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

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

GROUP# SEQUENCE# ARC STATUS

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

1 2 NO CURRENT

3 1 YES INACTIVE

2 0 YES UNUSED

SQL> alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 1

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance grs (thread 1)

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/grs/redo01.log'

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

System altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 75499088 bytes

Database Buffers 205520896 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> recover database until cancel;

ORA-00279: change 3273739 generated at 03/21/2014 16:07:22 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archivelog/archivelog/1_2_842803635.dbf

ORA-00280: change 3273739 for thread 1 is in sequence #2

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

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

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

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

1 1 1 52428800 1 NO CURRENT 3273740 21-MAR-14

2 1 0 52428800 1 YES UNUSED 0

3 1 0 52428800 1 YES UNUSED 0

[oracle@localhost grs]$ tail -f -n 20 /u01/app/oracle/admin/grs/bdump/alert_grs.log

select * from xx;----更新的丟失了

System parameters with non-default values:

processes = 150

__shared_pool_size = 62914560

__large_pool_size = 4194304

__java_pool_size = 4194304

__streams_pool_size = 4194304

sga_target = 285212672

control_files = /u01/app/oracle/oradata/grs/control01.ctl, /u01/app/oracle/oradata/grs/control02.ctl, /u01/app/oracle/oradata/grs/control03.ctl

db_block_size = 8192

__db_cache_size = 205520896

compatible = 10.2.0.1.0

log_archive_dest_1 = location=/u01/app/oracle/archivelog/archivelog/

db_file_multiblock_read_count= 16

db_recovery_file_dest = /u01/app/oracle/flash_recovery_area

db_recovery_file_dest_size= 2147483648

_allow_resetlogs_corruption= TRUE

undo_management = AUTO

undo_tablespace = UNDOTBS1

remote_login_passwordfile= EXCLUSIVE

db_domain =

dispatchers = (PROTOCOL=TCP) (SERVICE=grsXDB)

job_queue_processes = 10

background_dump_dest = /u01/app/oracle/admin/grs/bdump

user_dump_dest = /u01/app/oracle/admin/grs/udump

core_dump_dest = /u01/app/oracle/admin/grs/cdump

audit_file_dest = /u01/app/oracle/admin/grs/adump

db_name = grs

open_cursors = 300

pga_aggregate_target = 94371840

進行full expdp，然後重新創建db，在impdp（一些配置需要修改，比如歸檔，rman等）

SQL> alter database archivelog;

Database altered.

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog/archivelog/';

SQL> alter database open;

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archivelog/archivelog/

Oldest online log sequence 4

Next log sequence to archive 6

Current log sequence 6

SQL> show parameter user_dump_dest;

NAME TYPE VALUE

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

user_dump_dest string /u01/app/oracle/admin/grs/udum

SQL>

[oracle@localhost ~]$ export LC_ALL=en_US

[oracle@localhost ~]$ dbca

[oracle@localhost ~]$ netca

Oracle Net Services Configuration:

Oracle Net Services configuration terminated by user. The exit code is -1

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 24 15:46:01 2014

SQL> conn /as sysdba

Connected.

SQL> create directory expdp_file_dir as '/u02/oracleexp';

Directory created.

SQL> alter user scott account unlock;

User altered.

SQL> alter user scott identified by "987064";

User altered.

SQL> grant dba to scott;

Grant succeeded.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

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

With the Partitioning, OLAP and Data Mining options

[oracle@localhost ~]$ impdp scott/987064 directory=expdp_file_dir dumpfile=all_expdp_20140324.DMP nologfile=Y full=y parallel=4;

Import: Release 10.2.0.1.0 - Production on Monday, 24 March, 2014 15:57:38

UDI-00008: operation generated ORACLE error 1034

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux Error: 2: No such file or directory

UDI-00003: all allowable logon attempts failed

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 24 15:58:05 2014

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 92276304 bytes

Database Buffers 188743680 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

http://192.168.2.185:1158/em/console/

</collection>[oracle@localhost test_xml]$ emctl status dbconsole

TZ set to PRC

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

http://localhost.localdomain:1158/em/console/aboutApplication

Oracle Enterprise Manager 10g is running.

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

Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/localhost.localdomain_grs/sysman/log

9 歸檔模式，無備份，丟失一個數據文件

1 系統恢復

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 25 10:58:18 2014

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 113247824 bytes

Database Buffers 167772160 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> CREATE TABLESPACE data_test DATAFILE

'/u01/app/oracle/oradata/grs/data_test.dbf'SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 20M 2 ;

Tablespace created.

SQL> create table data_test(a number) TABLESPACE data_test;

Table created.

SQL> insert into data_test

2 select rownum from dual connect by rownum<=10;

10 rows created.

SQL> select * from data_test;

----------

10 rows selected.

SQL> commit;

Commit complete.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !rm '/u01/app/oracle/oradata/grs/data_test.dbf';

[oracle@localhost grs]$ rm data_test.dbf----二者一樣

SQL> startup;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 104859216 bytes

Database Buffers 176160768 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 10: '/u01/app/oracle/oradata/grs/data_test.dbf'

SQL> alter database create datafile '/u01/app/oracle/oradata/grs/data_test.dbf'as '/u01/app/oracle/oradata/grs/data_test.dbf';

-----------重建該文件，

Database altered.

SQL> recover datafile 10;---該數據文件從創建起的所有的redo文件還在，就可以修複

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from data_test;

----------

10 rows selected.

2 利用rman來恢復

重覆上述步驟到start

SQL> startup;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 113247824 bytes

Database Buffers 167772160 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 10: '/u01/app/oracle/oradata/grs/data_test.dbf'

[oracle@localhost grs]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 25 15:22:36 2014

connected to target database: GRS (DBID=341090406, not open)

RMAN> restore datafile 10;

Starting restore at 25-MAR-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

creating datafile fno=10 name=/u01/app/oracle/oradata/grs/data_test.dbf

restore not done; all files readonly, offline, or already restored

Finished restore at 25-MAR-14

RMAN> recover datafile 10;

Starting recover at 25-MAR-14

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 25-MAR-14

RMAN> alter database open;

database opened

RMAN> sql'select * from data_test';

sql statement: select * from data_test

也可以是,恢復表空間

run{

allocate channel c1 type disk;

restore tablespace users;

recover tablespace users;

sql 'alter database datafile 6 online';

release channel c1;

}

在丟失多個數據文件時：

當啟動檢查數據文件丟失報錯時可以查看該視圖看需要恢復的數據文件有哪些

select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

10 歸檔模式，有備份，丟失一個數據文件

RMAN> backup tablespace data_test format '/u01/oracle/backup/rman_back20140325_%U';

Starting backup at 25-MAR-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00010 name=/u01/app/oracle/oradata/grs/data_test.dbf

channel ORA_DISK_1: starting piece 1 at 25-MAR-14

channel ORA_DISK_1: finished piece 1 at 25-MAR-14

piece handle=/u01/oracle/backup/rman_back20140325_06p42qal_1_1 tag=TAG20140325T154005 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 25-MAR-14

Starting Control File and SPFILE Autobackup at 25-MAR-14

piece handle=/u01/oracle/backup/rman_backup_c-341090406-20140325-01 comment=NONE

Finished Control File and SPFILE Autobackup at 25-MAR-14

RMAN> exit;

Recovery Manager complete.

[oracle@localhost grs]$ rm data_test.dbf

SQL> insert into data_test

2 select rownum from dual connect by rownum<=10;

10 rows created.

SQL> select * from data_test;

----------

20 rows selected.

SQL> shutdown immediate;

ORA-01097: cannot shutdown while in a transaction - commit or rollback first

SQL> commit;

Commit complete.

SQL> shutdown immediate;

SQL> startup;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 117442128 bytes

Database Buffers 163577856 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 10: '/u01/app/oracle/oradata/grs/data_test.dbf'

SQL> set linesize 1000

SQL> set pagesize 1000

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

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

10 ONLINE ONLINE FILE NOT FOUND 0

------可以先open資料庫在offline，恢復-----減少停機時間

------可以執行rman進行恢復

SQL> alter database datafile 10 offline drop;

Database altered.

SQL>