oracle 备份恢复实例：三思笔记
系统归档模式，有备份
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
copyright (c) 1982, 2005, oracle. all rights reserved.
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后，被回滚
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
copyright (c) 1982, 2005, oracle. all rights reserved.
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>
[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.
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.
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
copyright (c) 1982, 2005, oracle. all rights reserved.
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
查看数据正常
--从新创建控制文件
在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
;
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、此方法适用于归档与非归档数据库。
总结，对于不是当前使用的归档日志损坏，归档模式需要使用alter database clear unarchived 命令清空日志 组即可。对于非归档模式需要使用alter system clear 日志文件组即可。
归档模式下当前日志的损坏有两种情况：
一、是数据库是正常关闭，日志文件中没有未解决的事务需要实例恢复，当前日志组的损坏就可以直接用alter database clear unarchived logfile group n 来重建。
二、是日志组中有活动的事务，数据库需要媒体恢复，日志组需要用来同步，有两种补救办
法：
a.最好的办法就是通过不完全恢复，可以保证数据库的一致性，但是这种办法要求在归档方式下，并且有可用的备份。
b.通过强制性恢复，但是可能导致数据库不一致。
下面分别用来说明这两种恢复方法
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.
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
p
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
copyright (c) 1982, 2005, oracle. all rights reserved.
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
copyright (c) 2003, 2005, oracle. all rights reserved.
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
copyright (c) 1982, 2005, oracle. all rights reserved.
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
copyright (c) 1996, 2005 oracle corporation. all rights reserved.
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
[oracle@localhost ~]$ sqlplus /nolog
sql*plus: release 10.2.0.1.0 - production on tue mar 25 10:58:18 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
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;
a
----------
1
2
3
4
5
6
7
8
9
10
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;
a
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
重复上述步骤到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
copyright (c) 1982, 2005, oracle. all rights reserved.
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
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;
a
----------
1
2
3
4
5
6
7
8
9
10
1
a
----------
2
3
4
5
6
7
8
9
10
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> alter database open;
database altered.
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_
如对本文有疑问， 点击进行留言回复！！
Oracle 基本概念 Dadabase,schema,user,table...
dbeaver连接Oracle中文乱码的解决方案--druid
Oracle常用语句（时间格式、去重、去特殊符号、查询表空间等）
我要评论
网友评论