非归档redo损坏的加_allow_resetlogs_corruption参数打开数据库
来源:互联网 发布:管理信息系统软件 编辑:程序博客网 时间:2024/05/07 20:30
Last login: Sun Feb 26 11:03:55 2012 from 128.192.0.254
[root@db ~]# pwd
/root
[root@db ~]# ps -ef | grep ora_
root 3284 2558 0 02:49 pts/0 00:00:00 grep ora_
[root@db ~]# su - oracle
[oracle@db ~]$ cd $ORACLE_HOME
[oracle@db db]$ ls
assistants config demo install.platform jlib md oc4j oracore perl relnotes sqlplus xdk
bin crs diagnostics inventory jre mesg odbc oraInst.loc plsql root.sh srvm
cdata css has javavm ldap mgw olap ord precomp root.sh.old sysman
cfgtoollogs ctx hs jdbc lib network OPatch oui racg slax uix
clone dbs install jdk log nls opmn owm rdbms sqlj wwg
[oracle@db db]$ cd dbs
[oracle@db dbs]$ ls
c-186369982-20120214-00 hc_prod.dat init.ora lkPROD orapwprod spfileEMREP.ora
db.sql hc_test.dat initprod.ora lkTEST orapwtest spfileprod.ora
hc_EMREP.dat initdw.ora lkEMREP orapwEMREP snapcf_prod.f spfiletest.ora
[oracle@db dbs]$ ls
c-186369982-20120214-00 hc_prod.dat init.ora lkPROD orapwprod spfileEMREP.ora
db.sql hc_test.dat initprod.ora lkTEST orapwtest spfileprod.ora
hc_EMREP.dat initdw.ora lkEMREP orapwEMREP snapcf_prod.f spfiletest.ora
[oracle@db dbs]$ export ORACLE_SID=test
[oracle@db dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 5 02:49:35 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/product/10.2/db/dbs/arch
Oldest online log sequence 9
Current log sequence 11
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 10 52428800 1 NO INACTIVE
195750 25-FEB-12
2 1 11 52428800 1 NO CURRENT
268422 05-MAR-12
3 1 9 52428800 1 NO INACTIVE
150733 24-FEB-12
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
1 STALE ONLINE
/u02/oradata/test/redo01.log
NO
2 ONLINE
/u02/oradata/test/redo02.log
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
/u02/oradata/test/redo03.log
NO
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL>
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 16 52428800 1 NO CURRENT
268589 05-MAR-12
2 1 14 52428800 1 NO INACTIVE
268585 05-MAR-12
3 1 15 52428800 1 NO INACTIVE
268587 05-MAR-12
SQL> shutdown about
SP2-0717: illegal SHUTDOWN option
SQL> shutdown abort
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@db dbs]$ mv /u02/oradata/test/redo01.log /u02/oradata/test/redo01.log.bak
[oracle@db dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 5 02:52:36 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 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: '/u02/oradata/test/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database until cancel;
ORA-00279: change 268589 generated at 03/05/2012 02:50:47 needed for thread 1
ORA-00289: suggestion : /oracle/product/10.2/db/dbs/arch1_16_776070180.dbf
ORA-00280: change 268589 for thread 1 is in sequence #16
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/oracle/product/10.2/db/dbs/arch1_16_776070180.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/oracle/product/10.2/db/dbs/arch1_16_776070180.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
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: '/u02/oradata/test/system01.dbf'
SQL> create undo tablespace undotbs2 datafile '//u02/oradata/test/undo02.dbf'size 20m;
create undo tablespace undotbs2 datafile '//u02/oradata/test/undo02.dbf'size 20m
*
ERROR at line 1:
ORA-01109: database not open
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> create undo tablespace undotbs2 datafile '/u02/oradata/test/undo02.dbf'size 20m
2 *
3 ;
*
*
ERROR at line 2:
ORA-02180: invalid option for CREATE TABLESPACE
SQL> create undo tablespace undotbs2 datafile '/u02/oradata/test/undo02.dbf'size 20m;
create undo tablespace undotbs2 datafile '/u02/oradata/test/undo02.dbf'size 20m
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/oradata/test/system01.dbf'
SQL> startup force;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open NORESETLOGS;
alter database open NORESETLOGS
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/oradata/test/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/10.2/db/dbs/sp
filetest.ora
SQL> !
[oracle@db dbs]$ ls
c-186369982-20120214-00 hc_prod.dat init.ora lkPROD orapwprod spfileEMREP.ora
db.sql hc_test.dat initprod.ora lkTEST orapwtest spfileprod.ora
hc_EMREP.dat initdw.ora lkEMREP orapwEMREP snapcf_prod.f spfiletest.ora
[oracle@db dbs]$ exit
exit
SQL> create pfile from spfile;
File created.
SQL> !
[oracle@db dbs]$ ls
c-186369982-20120214-00 hc_prod.dat init.ora lkEMREP orapwEMREP snapcf_prod.f spfiletest.ora
db.sql hc_test.dat initprod.ora lkPROD orapwprod spfileEMREP.ora
hc_EMREP.dat initdw.ora inittest.ora lkTEST orapwtest spfileprod.ora
[oracle@db dbs]$ vi inittest.ora
test.__db_cache_size=96468992
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__shared_pool_size=58720256
test.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/test/adump'
*.background_dump_dest='/oracle/admin/test/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u02/oradata/test/control01.ctl','/u02/oradata/test/control02.ctl','/u02/oradata/test/control03.ctl'
*.core_dump_dest='/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.job_queue_processes=10
*.local_listener='LISTENER_TEST'
*.open_cursors=300
*.pga_aggregate_target=31457280
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/test/udump'
_allow_resetlogs_corruption=ture
~
~
~
~
~
~
~
~
~
~
~
~
~
"inittest.ora" 25L, 841C written
[oracle@db dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 5 03:31:32 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> startup force
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 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: '/u02/oradata/test/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database until cancel;
ORA-00279: change 268589 generated at 03/05/2012 02:50:47 needed for thread 1
ORA-00289: suggestion : /oracle/product/10.2/db/dbs/arch1_16_776070180.dbf
ORA-00280: change 268589 for thread 1 is in sequence #16
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/oracle/product/10.2/db/dbs/arch1_16_776070180.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/oracle/product/10.2/db/dbs/arch1_16_776070180.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
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: '/u02/oradata/test/system01.dbf'
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/10.2/db/dbs/sp
filetest.ora
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@db dbs]$ ls
c-186369982-20120214-00 hc_prod.dat init.ora lkEMREP orapwEMREP snapcf_prod.f spfiletest.ora
db.sql hc_test.dat initprod.ora lkPROD orapwprod spfileEMREP.ora
hc_EMREP.dat initdw.ora inittest.ora lkTEST orapwtest spfileprod.ora
[oracle@db dbs]$ mv spfiletest.ora spfiletest.ora.bak
[oracle@db dbs]$ export ORACLE_SID=test
[oracle@db dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 5 03:33:05 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> startup force
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database RESETLOGS;
alter database RESETLOGS
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter database open RESETLOGS;
Database altered.
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance test (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u02/oradata/test/redo01.log'
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@db dbs]$ ls
c-186369982-20120214-00 hc_prod.dat init.ora lkEMREP orapwEMREP snapcf_prod.f spfiletest.ora.bak
db.sql hc_test.dat initprod.ora lkPROD orapwprod spfileEMREP.ora
hc_EMREP.dat initdw.ora inittest.ora lkTEST orapwtest spfileprod.ora
[oracle@db dbs]$ mv spfiletest.ora.bak spfiletest.ora
[oracle@db dbs]$ exit
exit
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
- 非归档redo损坏的加_allow_resetlogs_corruption参数打开数据库
- 【Oracle Log file 恢复】 已归档、非当前的 Redo LogFile 损坏
- 未启用归档数据库非数据文件(spfile,control,redo,undo,temp)全丢失的恢复方法
- 未启用归档数据库非数据文件(spfile,control,redo,undo,temp)全丢失的恢复方法
- 未启用归档数据库非数据文件(spfile,control,redo,undo,temp)全丢失的恢复方法
- redo文件丢失,没有开启归档归档 恢复 _allow_resetlogs_corruption"=true
- 用隐含参数强制打开了一个损坏的数据库!
- 数据库归档与非归档的转换
- 用热备+归档恢复损坏的非系统表空间
- ORACLE归档模式下的热备以及数据库打开状态状态下普通数据文件损坏后的还原
- 热备份 数据库打开状态下非系统表空间损坏的恢复
- 非归档模式下,redo丢失案例
- ORACE 非归档模式redo文件恢复
- oracle 未归档redo丢失或损坏(inactive状态)
- oracle 未归档redo丢失或损坏(active状态)
- 归档模式下redo丢失或损坏(inactive状态)
- 归档模式下redo丢失或损坏(active状态)
- 归档模式下redo丢失或损坏(current状态)
- poj3624
- myeclipse 运行速度慢的解决方案
- linux下部署erlang环境
- C语言中memset函数详解
- 为什么大多数程序员无法独立完成桌面软件
- 非归档redo损坏的加_allow_resetlogs_corruption参数打开数据库
- Oracle Sql 中常用字符串处理函数
- 删除MySQL二进制日志的方法
- 一个简单的C++程序
- 07.struts标签库
- 朴素认识Map和Reduce
- 冒泡排序(续)---用指针实现
- 后台长时间运行
- tar 解压缩命令