模拟当前redo日志损坏

最新推荐文章于 2023-05-15 21:07:25 发布

rgb_rgb

最新推荐文章于 2023-05-15 21:07:25 发布

阅读量2.7k

点赞数

分类专栏： ora

本文链接：https://blog.csdn.net/rgb_rgb/article/details/12012967

版权

ora 专栏收录该内容

41 篇文章 0 订阅

订阅专栏

SQL> create table tb1 as select * from dba_tables;

Table created.

SQL> insert into tb1 select * from tb1;

27206 rows created.

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1         91 2147483648        512          1 YES INACTIVE            4525020711 2013-09-24 13:07:55   4525236061 2013-09-25 00:54:25
         2          1         92 2147483648        512          1 NO CURRENT             4525236061 2013-09-25 00:54:25   2.8147E+14
         3          1         83 2147483648        512          1 YES INACTIVE            4525002210 2013-09-24 12:37:35   4525004380 2013-09-24 12:38:06
         4          1         84 2147483648        512          1 YES INACTIVE            4525004380 2013-09-24 12:38:06   4525006429 2013-09-24 12:38:36
         5          1         85 2147483648        512          1 YES INACTIVE            4525006429 2013-09-24 12:38:36   4525008520 2013-09-24 12:39:06
         6          1         86 2147483648        512          1 YES INACTIVE            4525008520 2013-09-24 12:39:06   4525012223 2013-09-24 13:05:42
         7          1         87 2147483648        512          1 YES INACTIVE            4525012223 2013-09-24 13:05:42   4525014297 2013-09-24 13:06:15
         8          1         88 2147483648        512          1 YES INACTIVE            4525014297 2013-09-24 13:06:15   4525016359 2013-09-24 13:06:45
         9          1         89 2147483648        512          1 YES INACTIVE            4525016359 2013-09-24 13:06:45   4525018537 2013-09-24 13:07:15
        10          1         90 2147483648        512          1 YES INACTIVE            4525018537 2013-09-24 13:07:15   4525020711 2013-09-24 13:07:55

10 rows selected.

SQL> shutdown abort
ORACLE instance shut down.

删除redo文件

SQL> startup
ORACLE instance started.

Total System Global Area 3.7413E+10 bytes
Fixed Size                  2229304 bytes
Variable Size            3.4226E+10 bytes
Database Buffers         3087007744 bytes
Redo Buffers               98418688 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '+DATA/sgerp5/onlinelog/group_2.504.826847151'
ORA-17503: ksfdopn:2 Failed to open file +DATA/sgerp5/onlinelog/group_2.504.826847151
ORA-15012: ASM file '+DATA/sgerp5/onlinelog/group_2.504.826847151' does not exist

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
P720:/home/oracle$cd $ORACLE_HOME
P720:/u01/app/oracle/product/11.2$cd dbs
P720:/u01/app/oracle/product/11.2/dbs$ls
hc_db11g.dat           init.ora               lkDB11G                orapwsgerp5            spfilesgerp5.ora.0916
hc_sgerp5.dat          initsgerp5.ora         lkSGERP5               snapcf_sgerp5.f
P720:/u01/app/oracle/product/11.2/dbs$ll
total 0
-rw-r--r--    1 oracle   oinstall       2851 May 15 2009 init.ora
-rw-r-----    1 oracle   oinstall         24 Sep 03 15:14 lkSGERP5
-rw-r-----    1 oracle   oinstall         24 Sep 04 10:47 lkDB11G
-rw-r-----    1 oracle   oinstall       3072 Sep 13 13:59 orapwsgerp5
-rw-r-----    1 oracle   oinstall       3584 Sep 16 13:43 spfilesgerp5.ora.0916
-rw-rw----    1 oracle   oinstall       1544 Sep 21 16:06 hc_db11g.dat
-rw-r--r--    1 oracle   oinstall       1111 Sep 22 16:30 initsgerp5.ora
-rw-r-----    1 oracle   oinstall   11091968 Sep 24 12:44 snapcf_sgerp5.f
-rw-rw----    1 oracle   oinstall       1544 Sep 25 13:33 hc_sgerp5.dat
P720:/u01/app/oracle/product/11.2/dbs$vi initsgerp5.ora
"initsgerp5.ora" 34 lines, 1111 characters
sgerp5.__db_cache_size=3087007744
sgerp5.__java_pool_size=134217728
sgerp5.__large_pool_size=268435456
sgerp5.__oracle_base='/u01/app'#ORACLE_BASE set from environment
sgerp5.__pga_aggregate_target=24427626496
sgerp5.__sga_target=13153337344
sgerp5.__shared_io_pool_size=0
sgerp5.__shared_pool_size=9261023232
sgerp5.__streams_pool_size=134217728
*.audit_file_dest='/u01/app/admin/sgerp5/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/sgerp5/controlfile/current.256.826820971','+DATA/sgerp5/controlfile/current.257.826820971'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_files=400
*.db_name='sgerp5'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=32212254720
*.diagnostic_dest='/u01/app'
*.distributed_lock_timeout=300
*.log_archive_dest_1='location=/oraarch'
*.log_archive_format='ARC_ERP5_%s_%r_%t.log'
*.memory_target=35G
*.open_cursors=1000
*.processes=800
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=100
*.sessions=885
*.undo_management='AUTO'
*.undo_retention=25200
*.undo_tablespace='UNDOTBS1'
*._allow_resetlogs_corruption=TRUE
*._allow_error_simulation=TRUE
"initsgerp5.ora" 36 lines, 1177 characters
P720:/u01/app/oracle/product/11.2/dbs$
P720:/u01/app/oracle/product/11.2/dbs$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 25 13:34:22 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3.7413E+10 bytes
Fixed Size                  2229304 bytes
Variable Size            3.4226E+10 bytes
Database Buffers         3087007744 bytes
Redo Buffers               98418688 bytes
SQL> alter database mount;

Database altered.

SQL> show parameter all

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_allow_error_simulation              boolean     TRUE
_allow_resetlogs_corruption          boolean     TRUE
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     785
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     512
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0
resource_manager_cpu_allocation      integer     32
SQL>
SQL>
SQL>
SQL> recover database;
ORA-10877: error signaled in parallel recovery slave

SQL> recover database until cancel;
ORA-00279: change 4525326070 generated at 09/25/2013 12:22:57 needed for thread
1
ORA-00289: suggestion : /oraarch/ARC_ERP5_92_826847122_1.log
ORA-00280: change 4525326070 for thread 1 is in sequence #92

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oraarch/ARC_ERP5_92_826847122_1.log'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log '/oraarch/ARC_ERP5_92_826847122_1.log'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 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: '+DATA/sgerp5/datafile/system.494.826840423'

SQL> alter database open resetlogs;

alter database open resetlogs
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> SQL> SQL>
SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3.7413E+10 bytes
Fixed Size                  2229304 bytes
Variable Size            3.4226E+10 bytes
Database Buffers         3087007744 bytes
Redo Buffers               98418688 bytes

Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 2148532224 bytes disk space from 32212254720 limit

SQL> alter system set db_recovery_file_dest_size=64GB;
alter system set db_recovery_file_dest_size=64GB
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> show parameter spfile;

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 25 14:28:22 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [1], [230358782], [1],
[230362287], [4194545], [], [], [], [], [], []
Process ID: 13762794
Session ID: 937 Serial number: 3

SQL> alter system switch logfile;
ERROR:
ORA-03114: not connected to ORACLE

SQL> shutdown immediate;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
P720:/u01/app/oracle/product/11.2/dbs$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 25 14:31:38 2013

Connected to an idle instance.

SQL> startup
ORACLE instance started.

COUNT(1)
----------
27206

SQL>